Override Locale Date with custom setings.

Question

Aside from the GL Support, is there a way to override locale settings with custom values for month and day when using mmm-dd-yyyy, modified spanish examples: Jan = ENE, Aug = AGO, or long dates (mmmm) January = ENERO, August = AGOSTO, or (dddd) Monday = LUNES, Thursday = JUEVES, etc.?

Answer 1

In Oracle:

SQL> alter session set nls_date_format = 'DAY, DD-MONTH-YYYY'
  2  /

Session altered.


SQL> alter session set nls_language='SPANISH'
  2  /

Session altered.


SQL> select sysdate as today, trunc(sysdate,'YYYY') as nyd from dual
  2  /

TODAY                         NYD
----------------------------- -----------------------------
DOMINGO  , 11-ABRIL     -2010 VIERNES  , 01-ENERO     -2010

SQL>

Answer 2

In SQL Server at least, you can use the SET LANGUAGE statement to set the language to one of the languages supported in the list of sys.syslanguages

SET LANGUAGE N'Spanish'

--mmm-dd-yyyy,
Select Left(DateName(mm, GetDate()),3)
    + '-' + Right('0' + Cast(DatePart(dd,GetDate()) As Varchar(2)), 2)
    + '-' + Cast(DatePart(yyyy, GetDate()) As char(4))

Note that there is no native date formatting function that will return the mmm-dd-yyyy format, so you have to build that yourself.

Answer 3

There are a number of ways to do it in Informix, but most of them work through the GLS facilities. Your question does not make it clear what your scenario is; there could be different answers depending on what you are attempting to do. Scenarios I can see include:

1. You have a system setup with, say, CLIENT_LOCALE=en_us.8859-15 but you want to see dates formatted as if CLIENT_LOCALE=es_es.8859-15 is in effect.
2. You have the Spanish setting in effect but you don't like the values it provides and want to use alternative names
3. You have the Spanish setting in effect but you want to see dates formatted differently from the default format.

Another important factor in the equation is whether DB_LOCALE is set the same as client locale.

Also, you should identify what you are using as the client API - again, there could be different answers depending on what you are using, and also how you are handling dates via the API (you get different results if you ask the API to return a DATE as a string rather than as a native 4-byte signed integer, for example; in the first case, the client API code will do a conversion to string automatically; in the second, your application code can use client API functions to format the value under your control).

IDS has an extensive set of functions to handle the formatting of DATE values. These include:

• DATE
• TODAY
• MDY
• DAY
• MONTH
• YEAR
• WEEKDAY
• TO_CHAR
• TO_DATE
• ADD_MONTHS