msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Use SQL to Create a Calendar in Mutliple Languages

In his post titled learning foreign languages with Oracle SQL, Matthias Rogel shares the following query (I slightly modified and formatted it for clarity):

  WITH calendar
       AS (    SELECT ADD_MONTHS (DATE '2012-01-01', LEVEL - 1) mon_date
                ,TO_CHAR (ADD_MONTHS (DATE '2012-01-01', LEVEL - 1), 'Month')
                         mon_name
                 FROM DUAL
           CONNECT BY LEVEL <= 12)
    SELECT nls.VALUE AS language
          ,calendar.mon_name AS mon_name
          ,TO_CHAR (
              calendar.mon_date
             ,'Month'
             ,q'[nls_date_language=']' || nls.VALUE || q'[']'
           )
              AS translated_mon_name
          ,TO_CHAR (
              calendar.mon_date
             ,'MON'
             ,q'[nls_date_language=']' || nls.VALUE || q'[']'
           )
              AS translated_mon_name_short
      FROM v$nls_valid_values nls, calendar
     WHERE nls.parameter = 'LANGUAGE'
  ORDER BY nls.VALUE, calendar.mon_date;

The output is a list of the 12 calendar months translated to different languages. If you do not have an Oracle database handy to run the query now, you can browse the result here.

Matthias uses several interesting techniques and features in his query:

Subquery Factoring: The WITH clause is used to name and separate the subquery that returns the 12 months. More about Subquery Factoring:

Row Generator: The CONNECT BY LEVEL method is used to generate 12 rows corresponding to the 12 months. More about generating rows:

Date Literal: DATE ’2012-01-01′is used to convert the string 2012-01-01 to a date datatype. More about date literals:

v$nls_valid_values: This view is used to select all available languages. More about v$nls_valid_values:

Cartesian Join: A Cartesian Join, a.k.a. cross join, is used to join between v$nls_valid_values and the calendar subquery. This assures returning 12 records (months) for each language. More about Cartesian Joins:

nls_date_language: The nls_date_language option in the to_char function is used to explicitly specify the language to use for the spelling of month names and abbreviations. More about nls_date_language:

Q-quote mechanism: q’[ is used to quote the the language in the to_char function. Q-quoting enables you to specify q or Q followed by a single quote and then another character to be used as the quote delimiter. More about the Q-quote mechanism:

This is just one relatively simple example of the power of SQL. For more examples of what you can do with SQL check out OraQA.com.


Filed in Oracle on 02 Apr 12


Reader's Comments

  1. |

    Colmnname eng_mon_name is a bit mislleading. The query

    SELECT ADD_MONTHS (DATE '2012-01-01', LEVEL - 1) mon_date
            ,TO_CHAR (ADD_MONTHS (DATE '2012-01-01', LEVEL - 1), 'Month')
                             mon_name
                     FROM DUAL
               CONNECT BY LEVEL <= 12
    
    translates names according to nls_language set for the session.