Query about Date Field conversion [message #2725] |
Thu, 08 August 2002 04:10 |
Dev
Messages: 28 Registered: May 2001
|
Junior Member |
|
|
Hi All,
I am having a varchar2 field date_Created which stores some timestamp in the format 'Wed Jun 19 19:54:29 EST 2002'.
I want to get the records which are greater than 15th July from this table.
Query I am devising is as follows:
select user_id,date_created from
where
to_char(to_date(replace(date_created,' EST',''),'Dy Mon DD HH24:MI:SS YYYY'))
> to_char(sysdate-1,'Dy Mon DD HH24:MI:SS YYYY')
But this is giving me following error :
ORA-01841: (full) year must be between -4713 and +9999, and not be 0.
While selecting
"select to_date(replace(date_created,' EST',''),'Dy Mon DD HH24:MI:SS YYYY'),user_id from
" works fine.
Where am I going worng? Please help as I will not be abl to move ahead witout it.
|
|
|
Re: Query about Date Field conversion [message #2731 is a reply to message #2725] |
Thu, 08 August 2002 09:18 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
There must be some data in your table that does not conform to this format.
Also, I would recommend that you just compare date datatypes in your WHERE clause:
where to_date(replace(date_created, ' EST'), 'Dy Mon DD HH24:MI:SS YYYY') > (sysdate - 1)
|
|
|