Speed Dating – NLS_DATE FORMAT in Oracle

When dealing with dates in a program, I’ll tend to err on the side of caution and explicitly do the conversion from a varchar to a date, specifying the format so there’s no chance of anything unexpected happening if the program should run in a session with a different NLS_DATE_FORMAT from that I’m using.
However, if for example, I need to do a one-off data fix, sometimes, I just can’t be bothered with all that typing.

So, to save me writing TO_DATE all the time, I’ll just use :

ALTER SESSION SET NLS_DATE_FORMAT='DDMMRR'

Time for a quick example.

Let’s say we have a table called birthdays :

CREATE TABLE birthdays(
    person VARCHAR2(50),
    dob DATE)
/

Now, when I come to populate this table with all those dates I simply mustn’t forget, do I can just do this …

ALTER SESSION SET NLS_DATE_FORMAT='DDMMRR'
/

INSERT INTO birthdays( person, dob)
VALUES( 'LARRY', '170844')
/

INSERT INTO birthdays( person, dob)
VALUES( 'STEVE B', '240356')
/

INSERT INTO birthdays( person, dob)
VALUES( 'LEO', '180953')
/

That was easy, look ….

SELECT person, TO_CHAR(dob, 'DD-MON-YYYY')
FROM birthdays
/
PERSON			       TO_CHAR(DOB,'DD-MON-YYYY')
------------------------------ ---------------------------------
LARRY			       17-AUG-2044
STEVE B 		       24-MAR-1956
LEO			       18-SEP-1953

Hmm, unless Oracle have acquired some Silicon Valley start-up that has IP rights on the fountain of youth (or possibly time travel), I think that may not have gone totally according to plan.

A better option may have been :

ALTER SESSION SET NLS_DATE_FORMAT='DDMMYYYY'
/

INSERT INTO birthdays( person, dob)
VALUES( 'LARRY', '17081944')
/

INSERT INTO birthdays( person, dob)
VALUES( 'STEVE B', '24031956')
/

INSERT INTO birthdays( person, dob)
VALUES( 'LEO', '18091953')
/

Now when we run the query we get :

SELECT person, TO_CHAR( dob, 'DD-MON-YYYY')
FROM birthdays;

PERSON			       TO_CHAR(DOB,'DD-MON-YYYY')
------------------------------ ---------------------------------
LARRY			       17-AUG-1944
STEVE B 		       24-MAR-1956
LEO			       18-SEP-1953

Most of the time, the RR format works fine. However, if you’re messing around with dates before the 50th year of a century, you may well get problems like this.
If the year number is less than 50, RR will assume that you’re talking about the current century. If it’s 50 or more, it’ll assume that you’re talking about the last century.
Not an issue you’re likely to come across every day I grant you, but useful to know if you’re trying to suck up to billionaire tech tycoons.