CODEWITHSUNDEEP
sqloracle-database
ylnsagar
ylnsagar

Reputation: 644

String 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.

Upvotes: 0

Views: 151

Answers (3)

user5683823
user5683823

Reputation:

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.

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

  1. The as date is in the wrong place.
  2. 'Aug' is a month name abbreviation in a certain language. Specify that language in to_char to make sure it works independent of your current setting.
  3. Don't only use YY when you have YYYY available.
  4. At last there is the time zone 'CDT'. Is it always 'CDT' or can it be something else? If you need to get from one time zone to another, you'd have to convert to timestamp with timezone first, then move to the other timezone, then convert to date.

Here is the query:

select * 
from
(
  select 
    cast(from_tz(cast("date" as timestamp), zone) at time zone 'CDT' as date) as "date"
  from 
  (
    select to_date(substr(t.timestamp,9,2) || '-' || 
                   substr(t.timestamp,5,3) || '-' || 
                   substr(t.timestamp,25,4), 
                   'DD-MON-YY', 
                   'NLS_DATE_LANGUAGE=AMERICAN') as "date",
           substr(t.timestamp,21,3) as zone
    from table t 
    where t.length(trim(t.timestamp)) = 28
  ) 
)
where "date" <= sysdate;

Despite its name from_tz doesn't convert from a timezone, but from a timestamp without timezone to a timestamp with timezone. So we use this to put our timezone information in. timestamp at time zone 'xyz' on the other hand moves the timezone to the one specified (the one we interprete our dates to reside in). cast is used to get from date to timestamp and vice versa.

Upvotes: 1

Mohamed El-Touny
Mohamed El-Touny

Reputation: 347

You can use TO_CHAR function to extract date formate https://docs.oracle.com/database/121/SQLRF/functions216.htm

Upvotes: -1

Related Questions