CODEWITHSUNDEEP
sqloracle-database
MeetJoeBlack
MeetJoeBlack

Reputation: 2904

ORACLE TIMESTAMP FORMAT MASK

maybe someone can help me with: I have date as input in that format

'2014-06-26T14:36:37.000+0000'

And i want to insert this date in column TIMECOL with TIMESTAMP(3) WITH TIME ZONE type, so i do this:

 INSERT INTO MY_TABLE(ID,NAME,TIMECOL) VALUES (NULL, 'TESTN',TO_TIMESTAMP('2014-06-26T14:36:37.000+0000', 'YYYY-MM-DD"T"HH24:MI:SS.FF3+TMZ'));

But i got error: ORA-01821: date format not recognized.

What mask i need to use?

Upvotes: 0

Views: 7820

Answers (2)

user2284452
user2284452

Reputation: 135

The trouble with this Oracle #$%%^ is that the formatting on the TO_TIMESTAMP_TZ and FROM_TZ is horrible. If you want to insert the value into a date field in a format you can actually use, try the below. You won't find this on the Oracle Support Forum.

-- The real format

SELECT to_date(to_char(from_tz(CAST (sysdate AS TIMESTAMP), 'US/Eastern') AT TIME ZONE 'UTC', 'MM/DD/YYYY HH12:MI:SS'), 'MM/DD/YYYY HH12:MI:SS') as MyDateToInsert from dual;

Upvotes: 1

Yigitalp Ertem
Yigitalp Ertem

Reputation: 2039

I guess you need to use TO_TIMESTAMP_TZ in order to get a timestamp with timezone. The following works:

select TO_TIMESTAMP_TZ('2014-06-26T14:36:37.000+0000', 'YYYY-MM-DD"T"HH24:MI:SS.FF3 TZD') from dual

So, your INSERT may be,

INSERT INTO MY_TABLE(ID,NAME,TIMECOL)
VALUES (NULL,
        'TESTN',
        TO_TIMESTAMP_TZ('2014-06-26T14:36:37.000+0000', 'YYYY-MM-DD"T"HH24:MI:SS.FF3 TZD'));

Upvotes: 2

Related Questions