How to Change Date Format in Oracle Database

Date Format defines how the dates display. Oracle database allows to change the date format using to_char and to_date functions. The date format specifies the format of the date to display in the outputs such as console window. Oracle database administrators may control the date format, while users can adjust it to control how dates appear in applications and task lists. The value of the NLS DATE FORMAT parameter in Oracle database determines the default date format. In the session, we may edit the parameter and pick any format we wish for the Oracle date. The alter session sql query allows to change the date format.



How to Change Date Format using to_char function

Use the built-in date function to_char to convert a date to the required format. To use the to_char function, you’ll need two parameters. A date should be the first parameter, and a string with a date format should be the second. The to_char function converts a date to a string in the format given, then returns the date as a string in that format.

select to_char(date_column_name, 'dd-mm-yyyy')  from dual;

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

Output

31-03-2022
31-03-2022 07:22:32
2022-mar-31


How to Change Date Format using to_date function

To convert a date string to a date value, use the to date built-in function. The date can be entered as a string with various formats. If the date is supplied in multiple formats, Oracle will be unable to grasp it. The to_date function converts a date into a date value using an alternative date format. Two arguments are required for the to date function. The date format and a date string.

select to_date(date_string_column_name, 'dd-mm-yyyy')  from dual;

select to_date('31-03-2022', 'dd-mm-yyyy')  from dual;
select to_date('31-03-2022 07:22:32', 'dd-mm-yyyy hh24:mi:ss')  from dual; 
select to_date('2022-mar-31', 'yyyy-mon-dd')  from dual;

Output

31-03-22
31-03-22
31-03-22


How to convert a date string from one format to another

To convert a date string from one format to another, use the Oracle built-in functions to char and to date. The example below demonstrates how to convert a date string from one format to another.

select to_char(to_date('03/31/2022','MM/DD/YYYY'), 'YYYY-MON-DD') from dual;

Output

2022-MAR-31


How to find Oracle default Date Format

The value of the NLS DATE FORMAT option determines the default date format in Oracle databases. The current value of the NLS DATE FORMAT parameter may be found with the following query.

select value from V$NLS_PARAMETERS where parameter = 'NLS_DATE_FORMAT';

Output

DD-MM-RR


How to change the date format for the session

The alter session command can be used to change the date format for the database connection session. The date format remains the same throughout the session as the chosen date format. To change the date format for the session, use the alter session command using the date format parameter.

select value from V$NLS_PARAMETERS where parameter = 'NLS_DATE_FORMAT';
select sysdate from dual;

alter session set nls_date_format = 'YYYY-MON-DD';

select value from V$NLS_PARAMETERS where parameter = 'NLS_DATE_FORMAT';
select sysdate from dual;

Output

DD-MM-RR
31-03-22

Session altered.

YYYY-MON-DD
2022-MAR-31


Date format specifiers

The following are the date format specifiers for the parts of the date.

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)



Leave a Reply