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

2003-03-11 Thread Basavaraja, Ravindra 
Correction

I am using 

select * from cust where
to_char(DATECREATED,'DD-MON-YY 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
  -Original Message-
 From: Basavaraja, Ravindra  
 Sent: Tuesday, March 11, 2003 12:19 PM
 To:   'Multiple recipients of list ORACLE-L'
 Subject:  sql query: to_date() :ORA-01830: date format picture ends before 
 converting entire input string
 
 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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

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

2003-03-11 Thread Jamadagni, Rajendra 
Title: RE: sql query: to_date() :ORA-01830: date format picture ends before 





Why are you comparing a date to a char? 


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


should work  it appears that your NLS_DATE_FORMAT kicked in when your query tried to do an implicit char to date conversion (on the LHS part of the where clause)

Raj
-
Rajendra dot Jamadagni at espn dot com
Any views expressed here are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !!



-Original Message-
From: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 11, 2003 3:20 PM
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: [EMAIL PROTECTED]


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: [EMAIL PROTECTED] (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).



This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*2

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

2003-03-11 Thread Richard Ji 
What's the to_char for?

try

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

-Original Message-
Sent: Tuesday, March 11, 2003 12:20 PM
To: Multiple recipients of list ORACLE-L
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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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: Richard Ji
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

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

2003-03-11 Thread Weiss, Rick 
You are comparing CHAR to DATE, you would need one of the following
conditions

1- where DATECREATED = to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')

2- where to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') = '01-FEB-03 07:53:28'

to be able to complete the WHERE clause

Rick Weiss
Oracle DBA


-Original Message-
Sent: Tuesday, March 11, 2003 13:20
To: Multiple recipients of list ORACLE-L


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: Weiss, Rick
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

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

2003-03-11 Thread Abdul Aleem 
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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).