| DECODE WITH DATE [message #52410] |
Fri, 19 July 2002 06:27  |
Keshav Tripathy
Messages: 30
Registered: November 2001
|
Member |
|
|
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: DECODE WITH DATE [message #52433 is a reply to message #52410] |
Mon, 22 July 2002 00:22  |
santosh
Messages: 85
Registered: October 2000
|
Member |
|
|
Hi keshav,
When i tried the excercise u have used in my oracle 8 it works absolutely fine. and gives no error. The problem i suspect in your case is that here u havent mention decode on the column In actual you are using it on a table column from where u are fetching a date records and putting them in defined format using decode function for null values or empty values.But have u checked what is the year for non null or non empty date values? If it is only entered previously as 07/11/39 oracle will assume it as 07/11/2039 so first u check that.
As far as decode function goes since you are entering values in full year format to_date('Jul-19-1939','mon-dd-yyyy') it works fine and will never give any error.
|
|
|
|
| Re: DECODE WITH DATE [message #52435 is a reply to message #52433] |
Mon, 22 July 2002 02:38 |
B
Messages: 327
Registered: August 1999
|
Senior Member |
|
|
|
and Verify your NLS_DATE .. and set it to YYYY if it's not already ( RRRR Means Y2K correction year 49 = year 2049 and year 51 = 1951 ... )
|
|
|
|
| Re: DECODE WITH DATE [message #55516 is a reply to message #52433] |
Tue, 28 January 2003 13:57 |
Partha
Messages: 4
Registered: September 2001
|
Junior Member |
|
|
The following snippet from OTN on DECODE behaviour would help understand this problem, which caught me off-guard also with a similar problem.
DECODE_expression::=
decode (expr, search, result,
search, result, ...
default);
"Oracle automatically converts the return value to the same datatype as the first result. If the first result has the datatype CHAR or if the first result is null, then Oracle converts the return value to the datatype VARCHAR2."
The problem arises from a combination of the above paragraph and the NLS_DATE_FORMAT setting on your system (typically 'DD-MON-RR', as it was on mine), as I try to explain with the following examples culled from the original posting.
select decode ('Jul-19-1939', '', NULL, to_date ('Jul-19-1939','mon-dd-yyyy')) DECODED_DATE
from dual
DECODED_DATE
------------
19-JUL-39 (character string)
The above is equivalent to:
select to_date ('Jul-19-1939','mon-dd-yyyy') TD from dual
followed by
select to_char (to_date ('Jul-19-1939','mon-dd-yyyy')) TC_TD from dual
End result: The character string '19-JUL-39', which would get stored in the database date field as 07/19/2039, due to the above NLS_DATE_FORMAT setting.
Solution that worked for me: Convert the datatype of the first result explicitly to DATE, as follows:
select decode ('Jul-19-1939', '', TO_DATE (NULL), to_date ('Jul-19-1939','mon-dd-yyyy')) DECODED_DATE
from dual
DECODED_DATE
------------
07/19/1939
Partha
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
