2

How to convert below string to Date in Oracle

Wed Jan 13 23:01:24 GMT 2016

Tried below and this get's date as 

SELECT TO_CHAR(SYSDATE, 'DY MON DD HH24:MM:SS YYYY') FROM dual;

FRI AUG 26 14:08:04 2016

Eventually, Wanted something like this

SELECT TO_CHAR('Wed Jan 13 23:01:24 GMT 2016', 'DY MON DD HH24:MM:SS ??? YYYY') FROM dual;
MoG
  • 404
  • 6
  • 16
  • 1
    Why sysdate,, and why to_char (without a format model!)? Are your strings always GMT or can they be different time zones? If they are not GMT what do you want to end up with - a timestamp with timezone, or a date with the local time, or a date with the time converted to UTC, or something else? – Alex Poole Aug 26 '16 at 18:50
  • Yes, they have different timezones. At the end thinking to bring all the dates to one single timezone ! – MoG Aug 26 '16 at 18:58
  • As Alex said, you will have a problem with GMT (besides the other things he pointed out). In Oracle, like it or not, "dates" (which are always date-times) do **not** have a time zone. If you enter this as a date, you will lose the time zone information. The only way to keep it is to enter it as a timestamp with timezone, or perhaps as a timestamp with local timezone, which means it will be converted from GMT to the server's timezone, and then stored as a timezone without the timestamp, but in a special datatype, "timestamp with local timezone". –  Aug 26 '16 at 19:01

2 Answers2

5

If the time zone is always a recognised abbreviation you can do:

select to_timestamp_tz('Wed Jan 13 23:01:24 GMT 2016', 'Dy Mon DD HH24:MI:SS TZD YYYY')
from dual;

TO_TIMESTAMP_TZ('WEDJAN1323:01:24GMT2016','DYMONDDHH24:MI:SSTZDYYYY')
---------------------------------------------------------------------
2016-01-13 23:01:24 EUROPE/LONDON

You can't convert directly to a date because the time zone format elements aren't allowed in to_date(). If you had a fixed value - always GMT - you could ignore that by treating it as a literal, but you don't.

If you want it as a timestamp or a date, rather than a timestamp with time zone, you need to decide how to convert it. You can assume it's local time and essentially ignore the time zone by casting it, or you can adjust it to a specific time zone, e.g. UTC. There are various ways, here are a couple with a CTE to provide your sample and one in another zone (well, in summertime anyway so you get a different string):

with t (str) as (
  select 'Wed Jan 13 23:01:24 GMT 2016' from dual
  union all select 'Fri Aug 26 19:53:27 BST 2016' from dual
)
select to_timestamp_tz(str, 'Dy Mon DD HH24:MI:SS TZD YYYY') as tstz,
  cast(to_timestamp_tz(str, 'Dy Mon DD HH24:MI:SS TZD YYYY') as timestamp) as ts,
  cast(to_timestamp_tz(str, 'Dy Mon DD HH24:MI:SS TZD YYYY') as date) as dt,
  sys_extract_utc(to_timestamp_tz(str, 'Dy Mon DD HH24:MI:SS TZD YYYY')) as tsutc
from t;

TSTZ                              TS                  DT                  TSUTC             
--------------------------------- ------------------- ------------------- -------------------
2016-01-13 23:01:24 EUROPE/LONDON 2016-01-13 23:01:24 2016-01-13 23:01:24 2016-01-13 23:01:24
2016-08-26 19:53:27 EUROPE/LONDON 2016-08-26 19:53:27 2016-08-26 19:53:27 2016-08-26 18:53:27

Exactly how you handle it depends on what you really need, of course.

Unfortunately that doesn't always work with abbreviations; Oracle doesn't necessarily recognise the values you see in Unix date command output, and the ones it does recognise aren't always available. Changing the session time zone can break it:

alter session set time_zone = 'America/Los_Angeles';
select to_timestamp_tz('Wed Jan 13 23:01:24 GMT 2016', 'Dy Mon DD HH24:MI:SS TZD YYYY')
from dual;

ORA-01857: not a valid time zone

You can change the session time zone to one that does recognise it (Europe/London) but that's a hack and won't work for all values anyway. It doesn't help that abbreviations can mean more than one thing.

If you have a list of known expected values and know what they really represent to you, you can swap the abbreviation for a region, but it doesn't really scale:

select to_timestamp_tz(
    replace(replace('Wed Jan 13 23:01:24 GMT 2016', 'GMT', 'Europe/London'),
      'BST', 'Europe/London'),
    'Dy Mon DD HH24:MI:SS TZR YYYY') from dual;

Or with multiple output formats:

with t1 (str) as (
  select 'Wed Jan 13 23:01:24 GMT 2016' from dual
  union all select 'Fri Aug 26 19:53:27 BST 2016' from dual
),
t2 (adj_str) as (
  select replace(replace(str, 'GMT', 'Europe/London'), 'BST', 'Europe/London')
  from t1
)
select to_timestamp_tz(adj_str, 'Dy Mon DD HH24:MI:SS TZR YYYY') as tstz,
  cast(to_timestamp_tz(adj_str, 'Dy Mon DD HH24:MI:SS TZR YYYY') as timestamp) as ts,
  cast(to_timestamp_tz(adj_str, 'Dy Mon DD HH24:MI:SS TZR YYYY') as date) as dt,
  sys_extract_utc(to_timestamp_tz(adj_str, 'Dy Mon DD HH24:MI:SS TZR YYYY')) as tsutc
from t2;

You'd need to have nested replace calls (or regexp_replace to reduce the repetition a little) for each abbreviation you expect; or could have a function that hides that mess away from your main query.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Tried this and getting an error at the first step: select to_timestamp_tz('Wed Jan 13 23:01:24 GMT 2016', 'Dy Mon DD HH24:MI:SS TZD YYYY') from dual; ORA-01857: not a valid time zone 01857. 00000 - "not a valid time zone" *Cause: *Action: – MoG Aug 26 '16 at 19:04
  • @MoG - which version of Oracle are you using? That works in 11.2.0.4. – Alex Poole Aug 26 '16 at 19:06
  • Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production – MoG Aug 26 '16 at 19:08
  • Ah, presumably since you don't appear to be in the UK, your session time zone isn't allowing that (`select sessiontimezone from dual`). Do you have a fixed list of possible time zone values - always GMT/BST for instance; or could they be anything? The Unix-date abbreviations aren't always recognised by Oracle anyway, so you may have to manually translate them to region names, which is painful. – Alex Poole Aug 26 '16 at 19:14
  • Planning to do it like this and also handle individual timezones in a CASE statement. I just have 3 different timezones for now. I guess it works .. SELECT DATE_OF_CHANGE, TO_DATE(TO_DATE(CONCAT( SUBSTR(DATE_OF_CHANGE, 1,20), SUBSTR(DATE_OF_CHANGE,25,4)), 'Dy Mon DD HH24:MI:SS YYYY'),'DD-MON-YYYY HH24:MI:SS') DATECONVERTED FROM TableName; – MoG Aug 26 '16 at 19:31
  • And convert to timestamp with time zone with `from_tz()`, passing in the result of your `case` as a region name? I guess that will work just as well as the replace option, yes. – Alex Poole Aug 26 '16 at 19:34
1

You need to use the TO_TIMESTAMP_TZ function.

The following example converts a character string to a value of TIMESTAMP WITH TIME ZONE:

SELECT TO_TIMESTAMP_TZ('1999-12-01 11:00:00 -8:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;

The -8:00 is the timezone.

https://docs.oracle.com/cd/B12037_01/server.101/b10759/functions179.htm#SQLRF06143

user3407335
  • 106
  • 1
  • 6