Truncating timestamps

sql oracle timestamp oracle11gr2
zerkms picture zerkms · Feb 24, 2011 · Viewed 13.7k times · Source

Let's suppose I have a timestamp variable:

select timestamp '2011-02-24 08:30:42 +06:00' from dual;

Is there any way to "truncate" it to something like

'2011-02-24 08:00:00 +06:00'

(I've cut minutes and seconds, but left the timezone)

The target oracle version is 11g r2

Answer

Shannon Severance picture Shannon Severance · Feb 24, 2011 
SQL> select to_timestamp_tz(to_char(timestamp '2011-02-24 08:30:42 +06:00', 'YYYY-MM-DD HH24 TZH:TZM'), 'YYYY-MM-DD HH24 TZH:TZM') from dual;

TO_TIMESTAMP_TZ(TO_CHAR(TIMESTAMP'2011-02-2408:30:42+06:00','YYYY-MM-DDTZH:
---------------------------------------------------------------------------
24.02.2011 8:00:00,000000000 +06:00

Related questions

ORA-01810: format code appears twice

Why is the sql below generating an ORA-01810 error? I researched the error and I am using different date formats for each date insert INSERT INTO bag_grte_clm ( schd_dprt_ldt, arr_trpn_stn_cd, bkg_crtn_gdt, sbmt_…

Read More
Add Day to Timestamp

How do I add days to a timestamp? If my timestamp is 01-JAN-2011 11-09-05 and I add 2 days, I want 03-JAN-2011 11-09-05.

Read More
Convert milliseconds to Timestamp

I know that to convert a Unix timestamp in milliseconds to an SQL timestamp I can use SELECT TO_DATE('1970-01-01','YYYY-MM-DD HH24:MI:SS') + (:timestamp / (1000*60*60*24)) FROM DUAL; But I need a Timestamp, so I tried with …

Read More