CODEWITHSUNDEEP
sqloracle-database
user836087
user836087

Reputation: 2501

How can I format an oracle query with this date?

I tried various alterations to get the right query date format. Below is one of them. The below date is actually filled in by string substitution from a java program. I need to be able to interpret it and run the query. Currently the query fails with the below error:

[Error] Execution (48: 18): ORA-01841: (full) year must be between -4713 and +9999, and not be 0

SELECT 
 to_date('Mon Jan 01 12:00:00 EST 1990', 'yyyy/mm/dd HH:MI:SS')
 FROM
 duAL

Upvotes: 0

Views: 76

Answers (3)

SNathan
SNathan

Reputation: 186

You cant use to_date for timezone. try this.

SELECT 
TO_TIMESTAMP_TZ('MON JAN 01 12:00:00 EST 1990', 'DY MON DD HH24:MI:SS TZR YYYY')
FROM
duAL;

Upvotes: 3

user2989408
user2989408

Reputation: 3137

[EDIT] Fixed the error, use TO_TIMESTAMP_TZ it should work.

SELECT 
TO_TIMESTAMP_TZ('Mon Jan 01 12:00:00 EST 1990', 'DY MON dd HH:MI:SS TZR yyyy')
FROM
duAL

Upvotes: -1

cavalsilva
cavalsilva

Reputation: 194

I try, removed the Mon and EST

SELECT to_date('Jan 01 12:00:00 1990', 'MON DD HH:MI:SS RRRR') FROM dual

Upvotes: 0

Related Questions