Alter Session Set nls_date_format | NLS_date_Format Tips

In my previous article i have given the details about different oracle errors with real life examples to resolve that errors. When I was working as database developer we are having issues in data conversion and the oracle date format is not matching with the system date format. There are so many times you might have faced issue when you require date format is different than the oracle displayed date format. Here we require to use Alter Session set nls_date_format to convert or to display date in specific date format.

What you will find in this article?

What is NLS_Date Format?

How to change date format using nls_date_format?

What is nls_date_format ?

There are so many time when developers using the to_Char or to_date function they are getting error in date format. The oracle has defined the specific date format you can call it as nls_date_format which specifies the default date format to use with to_char or to_date function. The NLS_DATE_FORMAT is enviromental variable and it does much more than changing the date format. It will also change the way to display the date in SQL plus or oracle SQL developer.

As example Real scenario :

If you want to display start date of employee in dd/mm/yyyy hh:mm format and need to do some time calculations. The nls_date_format has set to dd-mm-yyyy then the oracle engine will display date in to dd-mm-yyyy format.

How to change date using nls_date_format?

In this section we will see the particular examples of how to change the date format with its syntax and examples. I would like to explain you one real life industry example as well.

Syntax :

Alter session set nls_date_Format = ‘ date format’;

Real Example :

If our date is displaying in dd-mm-yyyy format in SQL developer and we require to do some calculations on time and need to set date in dd/mm/yyyy hh24:mm format. In this case we require to set nls_date_format ,

Alter session set nls_date_format = ‘dd/mm/yyyy hh24:mm’;

You can check the correct changed date format using ,

select sysdate from dual;

Output :

14/05/2021 12:26

You can also set only date or only timestamp also,

alter session set nls_date_format = ‘HH24:MI:SS’;

alter session set nls_date_format = ‘YYYY MM DD’;

We will check more examples of changing the date format. You can also use the use the to_char format mask to change the display value of a date or timestamp column:

select to_char(sysdate,’HH24:MI:SS’) from dual;
select to_char(sysdate,’YYY MM DD’) from dual;

The nls_date_format also allows you to display a date up to 1/100 of a second, quite precise date display:

alter session set nls_date_format = ‘DD-MON-YYYY HH24:MI:SS.FF’

There are so many times in industry where we require very precise date and time format. I would like to give the example of flight records. We require to maintain flight records in very precise date and timestamp. These are some examples of nls_date_format. Hope you get idea about how to change date format if it is not correct. If you like this article or if you have any issues with the same kindly comment in comments section.