How to change date format in oracle database

We often need to change the date format while checking. Here are some ways which you can use to change date format in Oracle database

Oracle Default Date Format

The default date format in oracle is controlled by the value of the NLS_DATE_FORMAT parameter.

The following query can be used to find the current value of the NLS_DATE_FORMAT parameter

SELECT
  value
FROM
  V$NLS_PARAMETERS
WHERE
  parameter = 'NLS_DATE_FORMAT';

How to change the date format for the whole session

We can change the parameter in the session and choose whatever format we want for the Oracle date. Here is the sql which can be used to do in the session

ALTER SESSION SET NLS_DATE_FORMAT = '[date_format]';
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)

Example

alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Now whatever query you will execute, the date column will be formatted as per the above date format

Example
select sysdate from dual;

How to change date format using to_char function

We may want to have different date format for the different sql queries.In that case, you can plan to use Oracle single row function to_char function and can convert oracle data column to whatever format you want

Example
select to_char(sysdate, 'dd-mon-yyyy hh24:mi:ss')  from dual; 
select to_char(sysdate, 'dd-mon-yyyy')  from dual;  

How to change date format using to_date function

We may want to change the strings into date format for comparison, for storing as date in Oracle table. In this case you can use to Oracle to_date function

 Example
select to_date('11-JAN-2019 13:12:12', 'dd-mon-yyyy hh24:mi:ss')  from dual; 
select to_date( '11-JAN-2019', 'dd-mon-yyyy')  from dual;   

Important thing to Note: the string is converted into date and date is returned as in default date format in select statement. If you are using to_date function for storing strings as date in date column, then that will be stored as date only. Now when select that column date will be displayed in oracle default date format set in session.

I hope you like this simple steps on how to change date format in oracle database. There can be many examples where this can be used. I have just give few simple examples to demonstrate the functionality