Date conversion problem 2

[kjnorman]

Okay, this one is driving me nuts.

In sqlplus the query:

select to_date(19960501,'yyyymmdd') from dual;

returns: 01-MAY-96

The query:

select to_char(to_date(19960501,'yyyymmdd'),'mm/dd/yyyy') from dual;

returns: 05/01/1996

This is what you would expect right? The thing that perplexes me is this. I have a procedure that performs a lot of date conversion from a cursor like:

decode(rec.F1DATA, '0', null, '99999999', null, to_date(rec.F1DATA,'YYYYMMDD'))

where rec.F1Data is the field of cursor rec and is like 19960501 or 20030622 for a date format.

When I run the procedure from my main oracle client tools, everything is okay, BUT if I run it from SQLPlus (plus80.exe) or from Oracle Sql worksheet, then the date are converted to the wrong century for dates before 2000. I.e. 19960501 gets converted to 5/1/2096.

This is most annoying. The nls_date_language is set to american and nls_date_format is set to "mm/dd/yyyy" in my init.ora file.

Any ideas?

Thanks
Kerry

 

[sem]

DECODE function can not return different TYPES of values for different cases, thus all the subsequent return values are casted to the type of the first one (null is character by default). The default date format is defined by NLS_DATE_FORMAT value. If you make some further assignments or if rec.F1DATA is not a string - other conversions may be implicitly done. Try

decode(rec.F1DATA, '0', to_date(null), '99999999', to_date(null), to_date(rec.F1DATA,'YYYYMMDD'))

or

to_date(decode(rec.F1DATA, '0', null, '99999999', null, rec.F1DATA),'YYYYMMDD')


Regards, Dima

 

[kjnorman]

Dima,

Thank you very much!

I tested both and both worked equally well, but I decided to implement the later

to_date(decode(rec.F1DATA, '0', null, '99999999', null, rec.F1DATA),'YYYYMMDD')

as it was shorter to code

This will save me lots of grief. Thank you again.
Kerry

 

[BJCooperIT]

Star earned!

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000