• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Sql » How to change date format in oracle database

How to change date format in oracle database

July 31, 2020 by techgoeasy Leave a Comment


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

Table of Contents

  • Oracle Default Date Format
  • How to change the format for the whole session
  • How to change date format using to_char function
  • How to change date format using to_date function

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 find the NLS_DATE_FORMAT parameter in oracle database

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 in oracle database using NLS_DATE_FORMAT parameter

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 in oracle database using to_char function

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;   
How to change date format in oracle database using to_date function

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


Filed Under: Oracle, Oracle Sql

Reader Interactions

Leave a Reply Cancel reply

Primary Sidebar

Top Posts & Pages

  • How to check Tablespace in Oracle -Size ,Free space,datafiles ,growth
  • Query to check table size in Oracle database
  • how to check all constraints on a table in oracle
  • Oracle Indexes and types of indexes in oracle with example
  • ORA-01017 Oracle Error Resolution Tips



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to run Autoconfig on Patch system in R12.2
  • Admin scripts in R12.2 Ebuisness Suite
  • Timezone settings in Oracle database
  • how to enable trace in oracle apps r12
  • Transparent Data Encryption Queries

Copyright © 2021 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us