Home » SQL & PL/SQL » SQL & PL/SQL » Query about Date Field conversion
Query about Date Field conversion [message #2725] Thu, 08 August 2002 04:10 Go to next message
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 Go to previous message
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)
Previous Topic: How to get OS version
Next Topic: Ok, I give up...
Goto Forum:
  


Current Time: Thu Mar 28 13:21:05 CDT 2024