trunc and timestamp

[sysdba]

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,

[abhaysk]

Look for To_Char, To_Date functions

[jmodic]

You might also want to check the EXTRACT() function - it returns the desired field from datetime value.

[akkerend]

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