-
trunc and timestamp
Hi,
trunc can only apply on date, but not timestamp.
I need portion of timestamp column, does anyone know how?
For example:
select sysdate, trunc(sysdate, 'month') from dual;
returns:
SYSDATE TRUNC(SYS
--------- ---------
23-AUG-04 01-AUG-04
select current_timestamp from dual;
returns:
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
23-AUG-04 09.56.02.354243 PM -06:00
I need somthing like:
select trunc(current_timestamp, 'month') from dual;
to give
01-AUG-04 instead of error:
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP
Oracle version 9.2.0.4.
Thanks,
-
Look for To_Char, To_Date functions
-
You might also want to check the EXTRACT() function - it returns the desired field from datetime value.
-
In 9i, use the CAST function:
SQL> select trunc(cast (systimestamp as date),'month') from dual;
TRUNC(CAS
---------
01-AUG-04
In 10g you can use trunc with timestamp:
SQL> select trunc(systimestamp,'month') from dual;
TRUNC(SYS
---------
01-AUG-04