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 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

Also Read
oracle date functions : This post covers oracle date functions, oracle date difference in years,oracle date difference in days, oracle date difference in months.
NULLIF function in Oracle :NULLIF function in Oracle compare two values and return null if the values are same,else return the first value.You can’t specify the literal NULL in values
Update statement in oracle :We use Update statement in oracle to modify the existing rows in the oracle table in the oracle database.Update can be executed in multiple ways
Coalesce Function in Oracle : Coalesce function in oracle will return first expression if it is not null else it will do the coalesce the rest of the expression.
how to get metadata of a table in oracle : Check out how to get table definition in oracle, oracle show index definition, get ddl of materialized view in oracle, get the query of a view in oracle
http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html