Oracle: Reset DATE to Epoch but not TIME

I needed a date string in the format: “1900-01-01 ” concatenated with the current TIME (less 1 hour)
eg.
current sysdate: “2009-08-31 14:42:14”
date required: “1900-01-01 13:42:14”

Firstly, the current time less 1 hour:

sysdate - numtodsinterval(1, 'hour')

Now, grab the TIME part of the above result

TO_CHAR(sysdate - numtodsinterval(1, 'hour')
  , 'HH24:MI:SS')

Now, Join the Epoch date to the above:

CONCAT('1900-01-01 ',
  TO_CHAR(sysdate - numtodsinterval(1, 'hour')
  , 'HH24:MI:SS'))

Convert the whole thing back to an Oracle DATE:

TO_DATE(
  CONCAT('1900-01-01 ',
  TO_CHAR(sysdate - numtodsinterval(1, 'hour')
  , 'HH24:MI:SS'))
  , 'YYYY-MM-DD HH24:MI:SS')

Test it out:

select TO_DATE(
  CONCAT('1900-01-01 ',
  TO_CHAR(sysdate - numtodsinterval(1, 'hour')
  , 'HH24:MI:SS'))
  , 'YYYY-MM-DD HH24:MI:SS')
from dual;

1900-01-01 13:42:14

Success! Pat myself on the back..

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s