0

My question has to do with the 5th command.

  1. create ex_table(on_date date);

  2. insert into ex_table values(to_date('23-aug-75','dd-mm-rr'));

  3. insert into ex_table values(to_date('23-aug-75','dd-mm-yy'));

After the applying the above 3 commands (executed in 2015):

  1. select to_char(on_date,'dd-mm-yyyy') from ex_table;

Results:

on_date
-------
23-aug-1975
23-aug-2075
  1. select to_char(on_date,'dd-mm-yyyy') from ex_table where on_date='23-aug-75';

Results:

on_date
-------
23-aug-1975

Why is the result of 5th command 23-aug-1975 and not 23-aug-2075 or both?

I am asking this because if command #2 is not executed, then the result would definitely contain 23-aug-2075.

sstan
  • 35,425
  • 6
  • 48
  • 66

2 Answers2

2

When you do

where on_date='23-aug-75'

It's actually translating that to:

where on_date=to_date('23-aug-75') -- without the fmt parameter.

Because no format is specified explicitly, the string gets parsed and converted to a date according to the current nls_date_format session value. Most likely, your session value is DD-MON-RR. You can check by querying:

select value
from nls_session_parameters
where parameter = 'NLS_DATE_FORMAT';

If that's the case, then the documentation on the The RR Datetime Format Element explains how the year is determined for the string 75 if the current date is 2015:

If the specified two-digit year is 50 to 99, then [...] If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.

So, applying the above rule, when you are applying the condition:

where on_date='23-aug-75'

... then it's looking for the date of August 23rd, 1975.

In your question you also say:

I am asking this because if command #2 is not executed, then the result would definitely contain 23-aug-2075.

I have to disagree with that statement. There is no way that your query will suddenly start matching to August 23rd, 2075, unless you change the nls_date_format session value to something else like DD-MON-YY.

So that's the explanation. But as for best practice, why not avoid the ambiguities altogether by always being explicit about the date format you are using and always using 4-digit year formats, or by using the ISO format YYYY-MM-DD which is so clear and easy to use.

sstan
  • 35,425
  • 6
  • 48
  • 66
0

RR stands for Century and YY stands for Year

You can check the date format of your database as suggested by sstan.

I have remembered it like this way.

Rule 1 :

If the current year is between 00-49 and you supplied a value between 00-49 then there will be no change in century i.e. 2015 being the current year and you passed a value 30 then 30 will be interpreted as 2030

Rule 2 :

If the current year is between 00-49 and you supplied a value between 50-99 then the century is present minus 1 i.e 2015 being the current year and you passed a value 75 then 75 will be interpreted as 1975.

Rule 3 :

Assuming the current year to be between 50-99 and you supplied a value between 00-49 then century will be century + 1 i.e. if current year is 2060 and you supplied a value 23 then it will be interpreted as 2161

Rule 4 :

Assuming the current year to be between 50-99 and you supplied a value between 50-99 then there will no change in century i.e. if current year is 2060 and you suppplied a value 56 then it will be interpreted as 2056

Sandeep
  • 774
  • 3
  • 8
  • i know about rr date format. I think u dint get my question.my question lie in final line –  Jul 24 '15 at 05:29
  • Since your datebase nls_date_format parameter is in DD-MON-RR format so all the insertion and comparison will happen on that basis. If command 2 is not executes then you won't be getting any result while executing command 5 – Sandeep Jul 24 '15 at 06:52