Happy New Year and fun with dates

As the calendar clicks over once more, it is time for some fun with date to character functions and function-based indexes.
This has been tested in XE (10gR2) and 11gR2 databases.

So nothing up my sleeves...

set echo on
start date_test.sql
clear screen
drop table date_format_test purge;
create table date_format_test (id number not null, val date not null); 
create index date_format_test_ix1 on date_format_test (
  id, to_char(val), to_char(val,'DD-Mon-YYYY'), to_char(val,'DD-fmMonth-YYYY'),  to_char(val,'DD-MM-YYYY'));

Insert a bunch of date values as they might come in from different sessions from different countries in different languages

  alter session set nls_date_format='DD/Mon/YYYY';
  alter session set nls_calendar='Gregorian';

 alter session set nls_language = 'English';
  insert into date_format_test values (1, date '2009-01-20');
  insert into date_format_test values (2, trunc(sysdate,'Mon'));
  insert into date_format_test values (3, to_date('25122009','ddmmyyyy'));

 alter session set nls_language = 'French';
  insert into date_format_test values (11, date '2009-01-20');
  insert into date_format_test values (12, trunc(sysdate,'Mon'));
  insert into date_format_test values (13, to_date('25122009','ddmmyyyy'));

 alter session set nls_language = 'German';
  insert into date_format_test values (21, date '2009-01-20');
  insert into date_format_test values (22, trunc(sysdate,'Mon'));
  insert into date_format_test values (23, to_date('25122009','ddmmyyyy'));

 alter session set nls_language = 'Spanish';
  alter session set nls_date_format='DD/fmMonth/YYYY';

Now lets see what's in the database...

  select /*+INDEX t*/ id, to_char(val,'DD-fmMonth-YYYY') from date_format_test t;

ID TO_CHAR(VAL,'DD-FMMONTH-YYYY')
----------- ------------------------------
1.00 20-January-2009
2.00 01-December-2009
3.00 25-December-2009
11.00 20-Janvier-2009
12.00 01-Décembre-2009
13.00 25-Décembre-2009
21.00 20-Januar-2009
22.00 01-Dezember-2009
23.00 25-Dezember-2009

  select /*+INDEX t*/ id, to_char(val,'DD-Mon-YYYY') from date_format_test t;
ID TO_CHAR(VAL,'DD-MON-
----------- --------------------
1.00 20-Jan-2009
2.00 01-Dec-2009
3.00 25-Dec-2009
11.00 20-Janv.-2009
12.00 01-Déc. -2009
13.00 25-Déc. -2009
21.00 20-Jan-2009
22.00 01-Dez-2009
23.00 25-Dez-2009

In both of those cases, the value 'preserved' in the function based index is in the language of the inserting session, so the querying session sees the value in a variety of languages rather than their own.

  select /*+INDEX t*/ id, to_char(val) from date_format_test t;
ID TO_CHAR(VAL)
----------- ------------------------------------------------
1.00 20/Enero/2009
2.00 01/Diciembre/2009
3.00 25/Diciembre/2009
11.00 20/Enero/2009
12.00 01/Diciembre/2009
13.00 25/Diciembre/2009
21.00 20/Enero/2009
22.00 01/Diciembre/2009
23.00 25/Diciembre/2009

In this cases, despite the hint, the data comes from the base table and is correctly shown in the language of the selecting session.
But this isn't just about display.

  select id, to_char(val,'DD-Mon-YYYY'), val from date_format_test t where to_char(val,'DD-Mon-YYYY') = '20-Jan-2009';

no rows selected

  select id, to_char(val,'DD-Mon-YYYY') from date_format_test where to_char(val,'DD-Mon-YYYY') = '20-Jan-2009';
ID TO_CHAR(VAL,'DD-MON-
----------- --------------------
1.00 20-Jan-2009
21.00 20-Jan-2009

In the first query, since VAL is selected, the data comes from the base table. No records are matched because, converting the date in the table to a character format in the current session language (Spanish) doesn't return any values matching the string '20-jan-2009'. In the second, it comes from the index and, because some inserting sessions were in English, we get some matches.

The problem is that function based indexes should only be used on deterministic functions and TO_CHAR(date,'format mask') doesn't necessarily meet the criteria. Being techies, we'd recognize that linguistic months might be a problem. We can always rely on numbers though, can't we ?
Well not quite. Did you notice my setup included setting NLS_CALENDAR ?

  alter session set nls_language = 'English';
  alter session set nls_calendar='Persian';

  select /*+INDEX t*/ id, to_char(val,'DD-MM-YYYY') from date_format_test t;
ID TO_CHAR(VA
----------- ----------
1.00 20-01-2009
2.00 01-12-2009
3.00 25-12-2009
11.00 20-01-2009
12.00 01-12-2009
13.00 25-12-2009
21.00 20-01-2009
22.00 01-12-2009
23.00 25-12-2009

  select id, val, to_char(val,'DD-MM-YYYY') from date_format_test t;
ID VAL TO_CHAR(VA
----------- ---------------------- ----------
1.00 01 Bahman 1387 01-11-1387
2.00 10 Azar 1388 10-09-1388
3.00 04 Dey 1388 04-10-1388
11.00 01 Bahman 1387 01-11-1387
12.00 10 Azar 1388 10-09-1388
13.00 04 Dey 1388 04-10-1388
21.00 01 Bahman 1387 01-11-1387
22.00 10 Azar 1388 10-09-1388
23.00 04 Dey 1388 04-10-1388

While you may think we are reaching the end of 2009, that isn't necessarily true.
But for those of us who are, then Happy New Year !

PS.
It is possible to specify the date language or the calendar as part of the to_char function. I suspect that would be sufficient to make these deterministic.

select to_char(sysdate,'DD-fmMonth-YYYY','nls_calendar=''Arabic Hijrah''') from dual;
select to_char(sysdate,'DD-fmMonth-YYYY','nls_date_language=''Italian''') from dual;