| Problem with Decode Function used with Date [message #21243] |
Fri, 19 July 2002 06:42  |
Keshav Tripathy
Messages: 30
Registered: November 2001
|
Member |
|
|
There is a Problem which is causing due to DECODE Function used with DATE Data Type. We have checked with RRRR Format as well as with NLS_DATE Format.
Please see the problem in detail below.
We have come across a peculiar problem while using decode function with date data type. When we are comparing the values we are finding it returns values
as follows.. for dates between year 1900 to 1949 it is
returning 2000 to 2049 respectively. In case of 1950 onwards its returning correct values.
To emulate the problem we did the following:
CREATE TABLE TAB1 (
TEST_DATE DATE)
And then we ran the following:
1) without using DECODE.
INSERT INTO TAB1 VALUES (to_date('Jul-19-1939','mon-dd-yyyy'));
TO_CHAR(TEST_DATE)
-------------------
1939/07/19
2) using DECODE with the date to be inserted being in the FALSE condition of DECODE statement.
INSERT INTO TAB1 VALUES (decode('Jul-19-1939','',NULL,to_date('Jul-19-1939','mon-dd-yyyy')));
TO_CHAR(TEST_DATE)
-------------------
2039/07/19
2) using DECODE with the date to be inserted being in the FALSE condition of DECODE statement with empty string as the condition.
INSERT INTO TAB1 VALUES (decode(' ','',NULL,to_date('Jul-19-1939','mon-dd-yyyy')));
TO_CHAR(TEST_DATE)
------------------
2039/07/19
2) using DECODE with the date to be inserted being in the TRUE condition of DECODE statement with empty string as the condition.
INSERT INTO TAB1 VALUES (decode('','',to_date('Jul-19-1939','mon-dd-yyyy'),NULL));
TO_CHAR(TEST_DATE)
-------------------
1939/07/19
The version of Oracle we am working with is ORACLE 8.1.7.0.0
Please reply us back how to handle such cases.
Thanks and regards.
Keshav
|
|
|
|
| Re: Problem with Decode Function used with Date [message #21248 is a reply to message #21243] |
Fri, 19 July 2002 13:31  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
I haven't tried your test case, but a few things you could try are:
1.) alter session set nls_date_format = 'yyyy-mm-dd';
This is to make sure that implicit datatype conversion is not causing your ploblem (set it very different to the format you are using).
2.) In 8i, '' is not equivalent to null.
3.) Decode takes on the datatype of the First return value in the decode. Null is varchar2 datatype.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
