Check and Change date format in oracle database

Check date format in Oracle

SQL>  select value from v$NLS_PARAMETERS where parameter = 'NLS_DATE_FORMAT';
VALUE
--------------------
DD-MON-RR

SQL> select sysdate from dual;
SYSDATE
---------
29-MAR-21

Change Date format at Session level

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI';
Session altered.

SQL> select sysdate from dual;
SYSDATE
-----------------
29-MAR-2021 20:00

Note: 
MM  Numeric month (e.g., 07)
MON     Abbreviated month name (e.g., JUL)
MONTH   Full month name (e.g., JULY)
DD  Day of month (e.g., 24)
DY  Abbreviated name of day (e.g., FRI)
YYYY    4-digit year (e.g., 1998)
YY  Last 2 digits of the year (e.g., 98)
RR  Like YY, but the two digits are ``rounded'' to a year in the range 1950 to 2049. Thus, 06 is considered 2006 instead of 1906
AM (or PM)  Meridian indicator
HH  Hour of day (1-12)
HH24    Hour of day (0-23)
MI  Minute (0-59)
SS  Second (0-59)

Change data format with function TO_CHAR in Query

SQL> select to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss')  from dual;
TO_CHAR(SYSDATE,'DD-MON-YYYYHH24:MI:SS')
-----------------------------------------
29-mar-2021 20:02:04

SQL> select to_char(sysdate, 'dd-mon-yyyy')  from dual;
TO_CHAR(SYSDATE,'DD-MON-YYYY')
-----------------------------------------
29-mar-2021

Change Data format with NLS_LANG environment variable

NLS_LANG is set to “JAPANESE_JAPAN.JA16SJIS” in regedit setting of Windows of client software.

SQL> select value from v$NLS_PARAMETERS where parameter = 'NLS_DATE_FORMAT';
VALUE
-----------------------------------------
RR-MM-DD

SQL> select to_char(sysdate, 'dd-mon-yyyy')  from dual;
TO_CHAR(SYSDATE,'DD-MON-YYYY')
-----------------------------------------
29-3îÄ -2021

SQL> select sysdate from dual;
SYSDATE
--------
21-03-29

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.