I need to insert a date format from an outside source which includes the three letter code for time zone, but the TZD formatting mask does not seem to work...
insert into blah
values (to_date('Thu, 18 Feb 2010 08:37:00 EST','Dy, DD Mon YYYY HH24:MI:SS TZD'));
ORA-01821: date format not recognized
If I remove the "TZD"...
insert into blah
values (to_date('Thu, 18 Feb 2010 08:37:00','Dy, DD Mon YYYY HH24:MI:SS'));
1 row created.
What is the proper mask for such an insert statement in Oracle?
desc blah
Name Null? Type
----------------------------------------- -------- ----------------------------
D DATE
Edit: I changed the table column from DATE type to TIMESTAMP type and got the same error.
You must use TO_CHAR(CAST(SYSTIMESTAMP AT TIME ZONE 'UTC' AS TIMESTAMP), '...')
For TIMESTAMP WITH LOCAL TIME ZONE data, Oracle converts the datetime value from the database time zone to UTC and converts back to the database time zone after performing the arithmetic. For TIMESTAMP WITH TIME ZONE data, the datetime value is always in UTC, so no conversion is necessary.
Oracle introduced TIMESTAMP data type in 9i version. It allows to store date time with fraction of seconds. By default format of TIMESTAMP is 'YYYY-MM-DD HH24:MI:SS. FF'.
Oracle stores dates in an internal numeric format representing the century, year, month, day, hours, minutes, seconds. The default date format is DD-MON-YY. SYSDATE is a function returning date and time. DUAL is a dummy table used to view SYSDATE.
Date columns don't have timezone as an option. You'd have to create the column as data type TIMESTAMP WITH TIME ZONE
orTIMESTAMP WITH LOCAL TIME ZONE
, and besides, the TO_DATE
function doesn't understand the TIME ZONE format mask you're applying.
SQL> CREATE TABLE T
2 (DT DATE,
3 TS TIMESTAMP,
4 TSTZ TIMESTAMP WITH TIME ZONE,
5 TSLTZ TIMESTAMP WITH LOCAL TIME ZONE);
Table created.
SQL> INSERT INTO T (TSLTZ) VALUES
2 (to_timestamp_tz('Thu, 18 Feb 2010 08:37:00 EST','DY, DD Mon YYYY HH24:MI:SS TZD'))
3 /
1 row created.
SQL> INSERT INTO T (TSTZ) VALUES
2 (to_timestamp_tz('Thu, 18 Feb 2010 08:37:00 EST','DY, DD Mon YYYY HH24:MI:SS TZD'))
3 /
1 row created.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With