Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

How to Set NLS_DATE_FORMAT for the Session in Oracle – Querychat

In this article, we will discover what is NLS in Oracle. We will learn what this acronym stands for and what the NLS data format is. Also, we will get to know how it can be set in Oracle for the user session.

NLS in Oracle: what is it?

NLS stands for National Language Support. This is an architecture, and it helps us to perform actions on data in our native languages, for example, processing or retrieving data.

Thanks to the use of NLS, we can not worry about the fact that the database will incorrectly display something in our data. For example, the date will be in the wrong format, or the currency units will indicate another country’s currency units. 

NLS architecture adjusts everything to our native language and our location, whether it is the  sorting order, date format, currency, or error messages.

What is NLS_DATE_FORMAT

NLS_DATE_FORMAT is a string parameter that is responsible for the default date format, which we are able to use with the TO_CHAR and TO_DATE functions.

However, the default value of this function goes from the NLS_TERRITORY parameter. 

This function, in turn, is responsible for the rules of days and weeks numbering in the database since it indicates the name of the territory. This function is also responsible for a number of other standard values.

The syntax of the NLS_DATE_FORMAT parameter is: 

NLS_DATE_FORMAT = "MM/DD/YYYY"

As we see, the value of this parameter can be any form of DATE format, and just the value must be specified in quotation marks.

Oracle examples on how to set NLS_DATE_FORMAT 

Let’s first use the command that changes the session parameter.

This command looks like:

ALTER SESSION SET NLS_DATE_FORMAT = <new_format>

Now we will check the value that is set in the database using a special view, namely the sys.v_$nls_parameters. 

sys.v_$nls_parameters is a view where the data like date and time are stored.

Our query is: 

SELECT * FROM sys.v_$nls_parameters

WHERE parameter='NLS_DATE_FORMAT'

This is the result we might receive:

Figure 1. The result of the NLS_DATE_FORMAT 

Now we will set the NLS_DATE_FORMAT for the user session by using the ALTER SESSION command. Let’s perform the following script: 

SELECT SYSDATE FROM DUAL;

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD';

SELECT SYSDATE FROM DUAL;

The result is:

Figure 2. NLS_DATE_FORMAT for the user session

Explanation:

First, the script returns the current date in the format that is set on the database.

After that, the ALTER SESSION command changes the parameter for the current session.

Next, we re-get the current date in a new format.

As we can see, we used the DUAL table in our query. The DUAL table is a system table.

Sometimes we may need to get the result of some stored function or the result of a calculation using a query. For this, this DUAL system table is useful. This table is always accessible to all users, and it always contains one column with the name DUMMY with VARCHAR2 (1) type and one row.

Instant livechat to an Expert!

Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service! 

Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc