How to truncate a timestamp datatype
I can't count the number of queries I've written over the years where trunc(date) was a very useful function. However, I can't seem to figure out how to accomplish the same when dealing with a timestamp datatype. According to the 10g SQL Reference, the "trunc()" function is supposed to support the timestamp types. However, my 10.2.0.4 database always converts the result to a date type.
Here is what I am basically trying to do:
SQL> select count(*)
2 from sys.aud$
3 where ntimestamp# > sys_extract_utc(trunc(systimestamp))
4 /
where ntimestamp# > sys_extract_utc(trunc(systimestamp))
*
ERROR at line 3:
ORA-30175: invalid type given for an argument
I have been able to figure out one way of obtaining a truncated timestamp:
SQL> select cast(trunc(sysdate) as timestamp with local time zone)
2 from dual
3 /
CAST(TRUNC(SYSDATE,'DAY')ASTIMESTAMPWITHLOCALTIMEZONE)
---------------------------------------------------------------------------
27-JUL-09 12.00.00.000000 AM
However, that is a mighty ugly approach. Am I missing something? Any and all help is appreciated!!
Stan