ylnsagar ylnsagar - 2 months ago 16
SQL Question

oracle time stamp to date conversion in query

i have the following "timestamp" in the column

Mon Aug 10 12:24:46 CDT 2016


so to convert into date, i am doing the following.

select * from
(select TO_DATE(SUBSTR(t.timestamp,9,2) || '-' || SUBSTR(t.timestamp,5,3) ||
'-' || SUBSTR(t.timestamp,27,2), 'DD-MON-YY') from table t where
t.LENGTH(TRIM(t.timestamp)) = 28 as date) where date <= sysdate;


The reason for doing that is, "Oracle sysdate" is returning a current date in the following format

20-SEP-16


So to compare the oracle sysdate, i am using the above approach.

is there any better approach for doing this, i knew this is inefficient using a group of sub-strings inside a select statement and since my "time stamp" value is too long, i am unable to convert to ORACLE date.

I am using oracle 11 as my Database. any help is appreciated.

Answer

Here is a proof of concept, assuming the so-called "timestamp" is in fact a string. If it is a proper timestamp with time zone (as it should be), then it's even simpler, you can compare to a date directly.

Note two things: In my mapping I don't have "CDT" for some reason but I do have the standard time zone, CST. I am probably missing a daylight savings time file which I don't care to hunt down and install. And Aug-10-2016 was a Wednesday; Monday won't work, you can't fool Oracle. Wonder why you didn't bother to use an actual, correct date (including the correct day of the week).

Edit: Actually I am not missing any "time zone codes file"; instead, to recognize CDT as a valid time zone, the TZR component in the model below needs to be changed to TZD.

PROOF OF CONCEPT:

select 'x' as col1
from   dual
where  to_timestamp_tz('Wed Aug 10 12:24:46 CST 2016', 
                       'Dy Mon dd hh24:mi:ss TZR yyyy') <=   sysdate
;



COL1
-----
x

1 row selected.