2011-05-09

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?

1 comment:

  1. You probably do not want to know this, but it is version specific !


    select version from v$instance;
    VERSION
    -----------------
    9.2.0.6.0

    select to_char(date '2000-01-01','Dy DD Mon YYYY','NLS_DATE_LANGUAGE=French') from dual;
    TO_CHAR(DATE'2
    --------------
    Sa 01 Jan 2000

    =================

    select version from v$instance;
    VERSION
    -----------------
    11.2.0.2.0
    select to_char(date '2000-01-01','Dy DD Mon YYYY','NLS_DATE_LANGUAGE=French') from dual;
    TO_CHAR(DATE'2000-
    ------------------
    Sam. 01 Janv. 2000



    You can also setup your own language by using $ORACLE_HOME/bin/lbuilder

    ReplyDelete

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.