varchar2 string to Date format

Question

I am trying to convert the below string value to TO_DATE but oracle is not recognizing the date format. Where am I going wrong.

SELECT TO_DATE('Wed Oct 10 23:50:00 2018 UTC','Dy Mon DD HH24:MI:SS YYYY TZR','NLS_DATE_LANGUAGE = American') 
FROM dual;

ERROR at line 1:

ORA-01821: date format not recognized

https://stackoverflow.com/questions/15833030/oracle-date-datatype-transformed-to-yyyy-mm-dd-hh24miss-tmz-through-sql#15834821 — Lukasz Szozda, Oct 17 '18 at 15:46
Are the string values always UTC? If so you could just ignore that part by treating it as a literal in the format mask. — Alex Poole, Oct 17 '18 at 17:59

score 3 · Answer 1 · answered Oct 17 '18 at 16:31

3

Use:

SELECT TO_TIMESTAMP_TZ('Wed Oct 10 23:50:00 2018 UTC','DY MON DD HH24:MI:SS YYYY TZR','NLS_DATE_LANGUAGE=American') 
FROM dual;

TO_DATE function returns DATE datatype, which does not support timezones. You are using TZR format specifier in your query (time zone region), and this generates the error because DATE does not support this.

This documentation - Time Zones shows which datatypes supports timezones, and states that only TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE do. So you must convert to one of this datatype instead of converting to DATE.

TO_TIMESTAMP_TZ function converts a literal to TIMESTAMP WITH TIME ZONE datatype.

answered Oct 17 '18 at 16:31

krokodilko

35,300
7
55
79

Thanks. That works. How do I get time difference in HH:MI:SS format for 2 dates in the above format : for example 'Wed Oct 10 23:50:00 2018 UTC' - 'Wed Oct 07 23:50:00 2018 UTC' – RaviNookala Oct 17 '18 at 17:07
@user3444050 - convert them both to timestamp with time zone as shown, and then either subtract them (to get an interval), or cast to dates if they're always in the same TZ and subtract those (to get a number of days). [This answer](https://stackoverflow.com/a/45601629/266304) has some pointers to get to the difference in your desired format, whichever way you do the subtraction. – Alex Poole Oct 17 '18 at 17:58

score 1 · Answer 2 · answered Oct 17 '18 at 18:08

If (and I realise it's a big 'if') the string values always contain UTC and not any other time zone values, then you could just treat that as a character literal. You would do that by changing your format model from TZR, which isn't recognised by to_date(), to "UTC" - including the double quotes:

SELECT TO_DATE('Wed Oct 10 23:50:00 2018 UTC',
  'Dy Mon DD HH24:MI:SS YYYY "UTC"',
  'NLS_DATE_LANGUAGE = American') 
FROM dual;

TO_DATE('WEDOCT1023
-------------------
2018-10-10 23:50:00

Of course, as that is a plain date it still has no time zone information, but if you want to retain that then you need a timestamp with [local] time zone data type anyway.

varchar2 string to Date format

2 Answers2