Convert data as per specific region

Scenario : Convert date as per specific region

Solution: 

 
Specifying an NLS parameter for an SQL function means that any User Session NLS parameters (or the lack of) will not affect evaluation of the function.

This feature may be important for SQL statements that contain numbers and dates as string literals. For example, the following query is evaluated correctly only if the language specified for dates is American:

SELECT ENAME FROM EMP
WHERE HIREDATE > '1-JAN-01'

This can be made independent of the current date language by specifying NLS_DATE_LANGUAGE:

SELECT ENAME FROM EMP
WHERE HIREDATE > TO_DATE('1-JAN-01','DD-MON-YY',
   'NLS_DATE_LANGUAGE = AMERICAN')


Using all numerics is also language-independent:

SELECT ENAME FROM EMP
WHERE HIREDATE > TO_DATE('1-01-01','DD-MM-YY')

NLS settings include Character set, Language and Territory

Common character sets:

WE8ISO8859P15 European English includes euro character
US7ASCII      American English

The DATE datatype always stores a four-digit year internally.

If you use the standard date format DD-MON-YY
YY will assume a year in the range 1900-1999 - it is strongly recommended you apply a specific format mask.