Home » SQL & PL/SQL » SQL & PL/SQL » Help with Date error
Help with Date error [message #197418] Wed, 11 October 2006 03:50 Go to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Hi,

i'm using a case statement in my code to display a string if the date (stored as a varchar) is a specific value.

the lines are:

Case When pds.dsschcompletion = '01/01/2099' Then 'TBA'  Else  pds.dsschcompletion End as dsschcompletion ,
Case When pds.dvschcompletion = '01/01/2099' Then 'TBA'  Else  pds.dvschcompletion End as dvschcompletion


when I run this I get the error:
ORA-01830 Date picture ends before converting entire input string


can anyone tell me why this is happening?

thanks,
Matt
Re: Help with Date error [message #197419 is a reply to message #197418] Wed, 11 October 2006 03:57 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Look at this:

matpj wrote on Wed, 11 October 2006 10:50

...the date (stored as a varchar)...
So you have a VARCHAR2.

matpj wrote on Wed, 11 October 2006 10:50

...Case When pds.dvschcompletion = '01/01/2099'

You compare it to a DATE without casting it to a DATE.

matpj wrote on Wed, 11 October 2006 10:50

can anyone tell me why this is happening?
Oracle tries to cast the VARCHAR2 to a DATE using your NLS_DATE_FORMAT (usually it is 'DD-MON-YYYY'). You, however have entered a date in a format different than that default date format. My advice: do a TO_DATE('01/01/2099','DD/MM/YYYY').

MHE
Re: Help with Date error [message #197421 is a reply to message #197419] Wed, 11 October 2006 04:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Some more advice would be not to store Dates in Varchar columns.
The DATE datatype exists for one purpose - to store dates. it does it really well, and avoids so many problems of the type you're getting.
Re: Help with Date error [message #197423 is a reply to message #197418] Wed, 11 October 2006 04:09 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Unfortunately, this 'date' field is transferred from a Notes database (where it is stored as a date) but the Lotus Enterprise Integrator tool I am using seems to have problems - and I then end up with a varchar2!!

when I convert the 01/01/2099 to a date I get exactly the same message
Sad
Re: Help with Date error [message #197444 is a reply to message #197423] Wed, 11 October 2006 05:02 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
But HOW do you convert it? Could you post your statement here? If TO_DATE is used properly, it shouldn't end up with an error
SQL> select to_date('01/01/2099', 'dd/mm/yyyy') ch_dat from dual;

CH_DAT
--------
01.01.99
Re: Help with Date error [message #197448 is a reply to message #197444] Wed, 11 October 2006 05:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If your date is indeed stored as a varchar2 you would not get this error, unless your case-statement has something in it you didn't include.
If it is a varchar2 as you say, you were comparing strings to strings, so there would be no need for an (implicit) conversion for Oracle.
Re: Help with Date error [message #197449 is a reply to message #197418] Wed, 11 October 2006 05:16 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
What is the format of pds.dsschcompletion ? If it is not a DATE column, it must be stored in a specific format (e.g. 'dd-mon-yyyy'). Find out what this and apply to_date to both sides of the statement e.g.

Case When to_date(pds.dsschcompletion, 'DD/MM/YYYY') = to_date('01/01/2099', 'DD/MM/YYYY') Then 'TBA'  Else  pds.dsschcompletion End as dsschcompletion

If that doesn't work, it is probably because there is a pds.dsschcompletion somewhere in the table which is in the wrong format.
Previous Topic: query for output in group
Next Topic: trigger and function
Goto Forum:
  


Current Time: Sat Dec 03 15:56:08 CST 2016

Total time taken to generate the page: 0.08809 seconds