Changing NLS Parameter Values for the Current SQL Function Invocation

SQL functions whose behavior depends on the values of NLS parameters are called locale-dependent. Some locale-dependent SQL functions have optional NLS parameters. These functions are:

• TO_CHAR

• TO_DATE

• TO_NUMBER

• NLS_UPPER

• NLS_LOWER

• NLS_INITCAP

• NLSSORT

In all of the preceding functions, you can specify these NLS parameters:

• NLS_DATE_LANGUAGE

• NLS_DATE_LANGUAGE

• NLS_NUMERIC_CHARACTERS

• NLS_CURRENCY

• NLS_ISO_CURRENCY

• NLS_DUAL_CURRENCY

• NLS_CALENDAR

• NLS_SORT

In the NLSSORT function, you can also specify these NLS parameters:

• NLS_LANGUAGE

• NLS_TERRITORY

• NLS_DATE_FORMAT

To specify NLS parameters in a function, use this syntax:

'parameter=value' ['parameter=value']...

Suppose that you want NLS_DATE_LANGUAGE to be AMERICAN when this query is evaluated:

SELECT last_name FROM employees WHERE hire_date > '01-JAN-1999';

You can set NLS_DATE_LANGUAGE to AMERICAN before running the query:

ALTER SESSION SET NLS_DATE_LANGUAGE=American;
SELECT last_name FROM employees WHERE hire_date > '01-JAN-1999';

Alternatively, you can set NLS_DATE_LANGUAGE to AMERICAN inside the query, using the locale-dependent SQL function TO_DATE with its optional NLS_DATE_LANGUAGE parameter:

SELECT last_name FROM employees
WHERE hire_date > TO_DATE('01-JAN-1999', 'DD-MON-YYYY',
                          'NLS_DATE_LANGUAGE=AMERICAN');