to_date not returning 4 digits for year, only 2

Question

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

@IlyaKogan no, I believe MON is correct. http://www.techonthenet.com/oracle/functions/to_date.php — user2924127, Nov 20 '15 at 19:43
I'm not an Oracle expert at all - but based on the name - doesn't `to_date` convert a *string* back into a *date* - which then is rendered in the default date formatting for your current installation...... if you want a specific formatting - wouldn't you have to convert a *date* **to** a *string* (applying the formatting you want) and then outputting that **string** ? — marc_s, Nov 20 '15 at 19:52
Check your nls_date_format session variable: http://stackoverflow.com/questions/1837974/oracles-default-date-format-is-yyyy-mm-dd-why. This might be an environmental issue. — , Nov 20 '15 at 19:57
Check the env variable as mentioned by @DanK. or if you don't have control or you want to handle it here then you can use to_char(myDate1, 'dd/mon/yyyy') to convert it back to string for displaying the result in desired format — Techie, Nov 20 '15 at 20:20
Try: `return to_char(myDate1, 'dd/mm/yyyy') || ' > ' || to_char(myDate2, 'dd/mm/yyyy') ....` — krokodilko, Nov 20 '15 at 20:41
Not directly related to your question but you might try: `to_date(myDate1_DD || myDate1_MON || myDate1_YY , 'DDMONYYYY')`. Seems a little silly to do the extra concatenation when it's not really necessary. — shawnt00, Nov 20 '15 at 21:48

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

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"

to_date not returning 4 digits for year, only 2

1 Answers1