0

Calling 

SELECT TO_DATE('Mon Sep 22 18:02:41 CDT 2014', 'DY MON DD HH24:MI:SS TZD YYYY') FROM Dual;

I get 

ORA-01821: date format not recognized
01821. 00000 -  "date format not recognized"

I put together the format string from these Oracle instructions. I tried both "TZR" and "TZD", neither works.

DY  Abbreviated name of day.
MON Abbreviated name of month.
DD  Day of month (1-31).
HH24    Hour of day (0-23).
MI  Minute (0-59).
SS  Second (0-59).
TZD Daylight savings information. For example, 'PST'
TZR Time zone region.
YYYY    4-digit year

Why is this not working?

Greg Dougherty
  • 3,281
  • 8
  • 35
  • 58
  • https://stackoverflow.com/questions/2291082/what-is-the-oracle-date-formatting-mask-for-time-zones Maybe use to_timeStamp_TZ? https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions194.htm – xQbert May 23 '17 at 19:03

1 Answers1

2

TO_DATE doesn't support timezone.

I think you need to use to_timestamp_TZ() to do what you're after...

SELECT To_TimeStamp_TZ('Mon Sep 22 18:02:41 2014 CDT', 'DY MON DD HH24:MI:SS YYYY TZD') 
FROM Dual;

Gives you something like (with my NLS paramaters)

22-SEP-14 06.02.41.000000000 PM AMERICA/CHICAGO

Also note the data type must be TIMESTAMP WITH TIME ZONE Data Type; or oracle just drops the timezone information w/o error.

https://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG238

xQbert
  • 34,733
  • 2
  • 41
  • 62
  • Now if only 1: Oracle were to document that TZD doesn't work with TO_DATE, and 2: To_TimeStamp_TZ could handle "EDT" – Greg Dougherty May 23 '17 at 19:48
  • It's there by omission does that count? :P (the fact that they don't talk about it means it's not supported right?) – xQbert May 23 '17 at 20:05
  • 1
    @GregDougherty - what do you mean by "only if Oracle were to document that TZD doesn't work with TO_DATE"? From the Oracle documentation: **The following datetime format elements can be used in timestamp and interval format models, but not in the original DATE format model: FF, TZD, TZH, TZM, and TZR.** https://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF00212 And no, I don't work for Oracle :-) –  May 23 '17 at 22:43
  • @mathguy Go to the directions for To_Date https://www.techonthenet.com/oracle/functions/to_date.php Now try to find any warning that TZ* don't actually work with To_Date. I don't see anything on that page saying that. Do you? – Greg Dougherty May 25 '17 at 18:16
  • @GregDougherty - Why would I go to "techonthenet" (who are they???) and not to the official Oracle web site? Someone who has nothing to do with Oracle may write whatever they want on the web, that is not considered "official Oracle documentation." –  May 25 '17 at 18:22
  • @mathguy Well, you're right, it's not Oracle. It is, however, the top hit on google when looking for Oracle To_date – Greg Dougherty May 25 '17 at 19:51