BAUD

Earlier I wrote about sqlplus client tool and date datatype. This post is about presenting dates.

"BAUD: Battle against Unclear Dataformats" Piet de Visser commenting on Alex Gorbachev tweet
"In the times of total globalization why people still battle with different date formats like 02/03/11 vs 03/02/11 - solution is 03-Feb-2011"



The solution is not alone 03-Feb-2011. NLS_LANGUAGE or NLS_DATE_LANGUAGE need to be fixed also.

Let us assume your date string is 03-Mar-2011. It might be that your session is talking POLISH.

SQL> alter session set nls_date_format='yyyy-mm-dd';

SQL> alter session set nls_language='POLISH';

SQL> select to_date('01-mar-2011','dd-mon-yyyy') from dual;

2011-03-01

SQL> alter session set nls_language='AMERICAN';

SQL> select to_date('01-mar-2011','dd-mon-yyyy') from dual;

2011-03-01

Ok that is fine. But if your data is in POLISH and you just happen to read a string in CROATIAN...

SQL> select to_date('01-lip-2011','dd-mon-yyyy','NLS_DATE_LANGUAGE=CROATIAN') from dual;

2011-06-01

SQL> select to_date('01-lip-2011','dd-mon-yyyy','NLS_DATE_LANGUAGE=POLISH') from dual;

2011-07-01

There are also another similar month abbreviations that may be converted to different months depending on language.

SQL> alter session set nls_date_format='yyyy-mm-dd';

SQL> select to_date('01-lis-2011','dd-mon-yyyy','NLS_DATE_LANGUAGE=CROATIAN') from dual;

2011-10-01

SQL> select to_date('01-lis-2011','dd-mon-yyyy','NLS_DATE_LANGUAGE=CZECH') from dual;

2011-11-01

SQL> select to_date('01-lis-2011','dd-mon-yyyy','NLS_DATE_LANGUAGE=POLISH') from dual;

2011-11-01




SQL> select to_date('01-srp-2011','dd-mon-yyyy','NLS_DATE_LANGUAGE=CROATIAN') from dual;

2011-07-01

SQL> select to_date('01-srp-2011','dd-mon-yyyy','NLS_DATE_LANGUAGE=CZECH') from dual;

2011-08-01

So in case of Feb, is it in AMERICAN, DANISH, DUTCH, ENGLISH, GERMAN, GERMAN DIN, ICELANDIC, ITALIAN, LATIN AMERICAN SPANISH, LATIN SERBIAN, LATVIAN, MALAY, MEXICAN SPANISH, NORWEGIAN, ROMANIAN, SLOVAK, SLOVENIAN, SPANISH or SWEDISH?