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 the 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 a 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 the 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 the 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;   

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

How to format date to display day, week, months etc

 Day of Week
 SQL> select to_char(sysdate,'D') col from dual; 
 col
----
 2

 Name of WeekDay
 SQL> select to_char(sysdate,'DAY') col from dual;
 col
---
MONDAY
 
Abbreviated name of day
 SQL> select to_char(sysdate,'DY') from dual;
 col
----
 MON
 
Day of Month
 SQL> select to_char(sysdate,'DD') from dual;
 col
----
 30
 
Abbreviated name of month
 SQL> select to_char(sysdate,'MON') from dual;
 col
----
 NOV
 
Name of Month
 SQL> select to_char(sysdate,'MONTH') from dual;
 col
-----
NOVEMBER

Day of Year
SQL> select to_char(sysdate,'DDD') from dual;
 col
----
 334
 
Week of the year
SQL> select to_char(sysdate,'IW') from dual;
 col
----
 05

Week of Month
 SQL> select to_char(sysdate,'W') from dual;
 col
-----
 5

Month in Numbers
SQL> select to_char(sysdate,'MM') from dual;
 col
----
 11

Year in Numbers
SQL> select to_char(sysdate,'YYYY') from dual;
 col
----
 2019
 
Year in words
SQL> select to_char(sysdate,'YEAR') from dual;
col
---
TWENTY NINE

Hour in 0-12 format
SQL> select to_char(sysdate,'HH') from dual'
col
----
08

Hour in 0-24 format
SQL> select to_char(sysdate,'HH24') from dual;
col
---
 12

Minutes
 SQL> select to_char(sysdate,'MI') from dual;
 col
----
 45
 
Seconds
 SQL> select to_char(sysdate,'SS') from dual;
 col
----
 45
 

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