Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to truncate a timestamp datatype

Stan - HerrmanJul 27 2009 — edited Jul 30 2009
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
This post has been answered by SanjayRs on Jul 27 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 27 2009
Added on Jul 27 2009
13 comments
15,189 views