CODEWITHSUNDEEP
sqldateplsql
user2924127
user2924127

Reputation: 6252

to_date not returning 4 digits for year, only 2

I am trying to output a date in the format DD/MON/YYYY where the year has 4 digits. My variable return a year with 4 digits (e.g 2000, not 00), but when I run it through the to_date function it seems to be assigning it as a 2 digit.

I tried to show output to isolate the input I am giving for the year and it is outputting that the year variable is 4 digits so I am not sure what is going wrong.

This is my code:

myDate1 := to_date(myDate1_DD || '/' || myDate1_MON || '/' || myDate1_YY , 'DD/MON/YYYY'); 
myDate2 := to_date(myDate2_DD || '/' || myDate2_MON || '/' || myDate2_YY , 'DD/MON/YYYY');

IF  myDate1 > myDate2  
THEN
    -- return '01/JAN/01 > 01/JAN/99. The year for date1 is 2001'
    return myDate1 || ' > ' || myDate2 || '. The year for date1 is  ' || myDate1_YY;
ELSE
    -- return '01/JAN/03 > 01/JAN/02. The year for date2 is 2003'
    return myDate2 || ' > ' || myDate1 || '. The year for date2 is  ' || myDate2_YY;
END IF;

So essentially instead of an output like 01/JAN/03, I would like an output like 01/JAN/2003.

Cheers

Upvotes: 1

Views: 3533

Answers (1)

ARC
ARC

Reputation: 353

You're seeing the default date format specified in NLS_DATE_FORMAT.

NLS_DATE_FORMAT specifies the default date format to use with the TO_CHAR and TO_DATE functions. The default value of this parameter is determined by NLS_TERRITORY.

The value of this parameter can be any valid date format mask, and the value must be surrounded by double quotation marks. For example:

NLS_DATE_FORMAT = "MM/DD/YYYY"

To see what yours is:

SELECT value FROM v$nls_parameters WHERE parameter ='NLS_DATE_FORMAT';

See kordirko's comment on getting the actual date format you want:

return to_char(myDate1, 'dd/MON/yyyy') || ' > ' || to_char(myDate2, 'dd/MON/yyyy');

Example with dbms_output rather than return:

DECLARE
  myDate1_DD VARCHAR2(10) := '01';
  myDate1_MON  VARCHAR2(10) := 'JAN';
  myDate1_YY   VARCHAR2(10) := '2001';
  
  myDate2_DD VARCHAR2(10) := '01';
  myDate2_MON VARCHAR2(10) := 'JAN';
  myDate2_YY   VARCHAR2(10) := '2003';

  myDate1 DATE;
  myDate2 DATE;
BEGIN
myDate1 := to_date(myDate1_DD || '/' || myDate1_MON || '/' || myDate1_YY , 'DD/MON/YYYY'); 
myDate2 := to_date(myDate2_DD || '/' || myDate2_MON || '/' || myDate2_YY , 'DD/MON/YYYY');

IF  myDate1 > myDate2  
THEN
    -- return '01/JAN/01 > 01/JAN/99. The year for date1 is 2001'
    DBMS_OUTPUT.put_line( to_char(myDate1, 'dd/MON/YYYY') || ' > ' || to_char(myDate2, 'dd/MON/YYYY') || '. The year for date1 is  ' || myDate1_YY);
ELSE
    -- return '01/JAN/03 > 01/JAN/02. The year for date2 is 2003'
    DBMS_OUTPUT.put_line( to_char(myDate2, 'dd/MON/YYYY') || ' > ' || to_char(myDate1, 'dd/MON/YYYY') || '. The year for date2 is  ' || myDate2_YY);
END IF;
END;
/

with those inputs, dbms_output result is: "01/JAN/2003 > 01/JAN/2001. The year for date2 is 2003"

Upvotes: 2

Related Questions