Oracle-L: RE: sql query: to_date() :ORA-01830: date format picture ends bef

From: Abdul Aleem <dmit_at_beaconhouse.edu.pk>
Date: Tue, 11 Mar 2003 21:58:41 -0800
Message-ID: <F001.00567229.20030311215841@fatcity.com>

Ravindra,

All the solutions given by others are correct.

The reason for the error is that: In your where clause the date that you are converting to char, is re-converted to date for comparison with a date value. This conversion uses Oracle's implicit date conversion, the implicit date conversion uses current NLS setting which by default is of two digit year and without time component.

So if you remove time and its format from your to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS'), it should work. Or if you change your NLS settings to include time component the same command should work.

HTH! Aleem

-----Original Message-----

Sent:	Wednesday, March 12, 2003 1:20 AM
To:	Multiple recipients of list ORACLE-L
Subject:	sql query: to_date() :ORA-01830: date format picture ends

before

Hi...

I am getting the following error with a query like this

select * from cust where
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') = to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')

ORA-01830: date format picture ends before converting entire input string

What is the problem?

Thanks
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Basavaraja, Ravindra
INET: Ravindra.Basavaraja_at_T-Mobile.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Abdul Aleem
INET: dmit_at_beaconhouse.edu.pk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------