Oracle to_char Date Format

In this Oracle tutorial, we will learn about the oracle to_char date format using various examples. Additionally, we will also cover these topics:

• Oracle to_char date format
• Oracle to_char date format options
• Oracle to_char date format milliseconds
• Oracle to_char date format microseconds
• Oracle to_char date format am/pm
• Oracle to_char date form at in where clause
• Oracle to_char date format month
• Oracle to_char date month name
• Oracle to_char date format day of week
• Oracle to_char date format timezone
• Oracle to_char date format examples

Oracle to_char date format

Oracle to_char date format is used to convert the date format to string data type. This method is reverse of to_date() in the oracle database.

Below is the syntax of the to_char() method in the oracle database, in this syntax DateTime, must be as per the specified format. If no format is provided then it picks the NLS_DATE_FORMAT which is mostly DD-MON-YY. To change language use NLS_DATE_LANGUAGE = [language of choice].

TO_CHAR(DATETIME, FORMAT, NLS_DATE_LANGUAGE=<lang>)

For DateTime values in the to_char() method, you can use the following format specifier in oracle database 19c & 21c.

Oracle TO_CHAR	Format Specifier
DL	Day name, Month Day, Year
DD	Day (1 – 31)
DY	Abbreviated day (Sun-Sat)
MM	Month (1 – 12)
MON	Abbreviated month (Jan – Dec)
MONTH	Month name (January – December)
YY	2-digit year
YYYY	4-digit year
HH or HH12	Hour (1 – 12)
HH24	Hour (0 – 23)
MI	Minutes (0 – 59)
SS	Seconds (0 – 59)

Example:

In our example, we have displayed date in string format using the to_char() method in oracle database 19c & 21c.

We are fetching the system date and then displaying it in a specified format. Bold text is the output of the query. We have executed twice with a different format in the oracle database.

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY')
  AS "CURRENT DATE"
  FROM dual;

CURRENT DATE
--------------------
24-JAN-2022

SELECT TO_CHAR(SYSDATE, 'DD MON, YYYY')
  AS "CURRENT DATE"
  FROM dual;

CURRENT DATE
---------------------
24 JAN, 2022

Read: How to Check Oracle Database Version

Oracle to_char date format options

Oracle to_date is widely used for converting DateTime created using date, timestamp, timestamp with timezone, etc to varchar2 datatype in oracle database 19c & 21c.

Oracle to_date() method as mainly 3 options as shown in the below syntax:

TO_DATE(
    DATETIME,
    FOMAT, 
    NLS_DATE_LANGUAGE
)

• DATETIME: day, month, year and time must be in the defined format. In case, no format is defined then it picks the NLD_DATE_FORMAT.
• FORMAT: It defines the way date, month, year and time will be stored in the database.
• NLS_DATE_LANGUAGE: December could be written in different ways in different part of the word. By specifying regional language will allow you to store datetime in your regional langage.

Click here to view the available options for nls_date_language.

Example:

In our example, we have displayed date in 3 different NLS language formats (Canadian french, German Din, and Latin American Spanish). Also, we have covered the remaining 2 options in each example.

Script:

ALTER SESSION SET NLS_DATE_LANGUAGE = 'CANADIAN FRENCH';


SELECT TO_CHAR(sysdate, 'DDMONTHYYYY')
  AS "CURRENT DATE"
  FROM dual;



ALTER SESSION SET NLS_DATE_LANGUAGE = 'GERMAN DIN';

SELECT TO_CHAR(sysdate, 'DDMONTHYYYY')
  AS "CURRENT DATE"
  FROM dual;


ALTER SESSION SET NLS_DATE_LANGUAGE = 'LATIN AMERICAN SPANISH';

SELECT TO_CHAR(sysdate, 'DDMONTHYYYY')
  AS "CURRENT DATE"
  FROM dual;

Output:

Today is 24th January 2022, but since month names are written in different ways over the globe that is why when we set the NLS_DATE_LANGUAGE to Canadian french, german din, or Latin American Spanish. All of them gave us January but in their regional language.

Read: How to Get List all Tables in Oracle

Oracle to_char date format milliseconds

Milliseconds is microseconds divided by 1000 (milliseconds = microseconds / 1000). And 1 second has 1000 milliseconds. Oracle date data types do not support milliseconds but Oracle timestamps do.

In case you are dealing with the database that has a date data type and your task is to find the milliseconds for the same then it won’t be possible because while storing that data millisecond was not captured by date data type.

Fetching of milliseconds is only possible if the data type is Timestamp. So if you are writing a database for NASA use Timestamp data type to record even milliseconds.

• In our explanation, we will demonstrate oracle to_char date format milliseconds using Timestamp.
• Use .ff or .FF after seconds to fetch the milliseconds from the database dd.mm.rrrr hh:mi:ss.ff or DD.MM.RRRR HH24:MI:SS.FF
• In case you want to fetch datetime in milliseconds from the system then use SYSTIMESTAMP.
• There is unclear infomation about Oracle to_char date format microseconds. As none of the oracle official documentation deliver the concrete information on this topic.
• For now you can use content of this section for Oracle to_char date format microseconds. We will update this section if found any legitimate info.
• Also, if you have information of Oracle to_char date format microseconds please do write us.

Example:

In our example, we have displayed milliseconds in the oracle database. Run this script multiple times in a row to view the change in time.

SELECT TO_CHAR (SYSTIMESTAMP, 'DD.Mon.YYYY HH:MI:SS.FF') 
AS RESULT
FROM DUAL;

Output:

The red marked area is the output of the script. We have run the same script multiple times to display the change in milliseconds. Yellow marked numbers are milliseconds.

Read: Connect Excel to Oracle Database

Oracle to_char date format am/pm

Ante Meridiem (AM) is the 12 hours period that starts from midnight and lasts till noon. After that Post Meridiem (PM) starts for the next 12 hours and ends before midnight.

In oracle to_char format can be displayed only if the time format is set to 12 hours. Add AM or PM at the end of the statement after seconds.

In the below example, we have applied AM/PM on the current date and time. We are using sysdate to fetch date and time from the system also time format is set to 12 hours.

SELECT TO_CHAR(sysdate, 'DD-MM-YYYY HH:MI:SS AM') 
As "Date format AM/PM"
FROM dual;

Output:

In the below output, Date, time, and AM/PM format is displayed using to_char format in the oracle database. The output is marked with red.

Read: How to create a database in Oracle 19c

Oracle to_char date format in where clause

Where clause is used to apply condition on the oracle query to fetch specific information from the oracle database 19c & 21c.

Where clause can be used after from clause in oracle database. In the below example, the 1-year salary slip of an employee in the United States of America is displayed.

We have shared the script to create a table, insert dummy data, and view the inserted data. After that, using the Where clause we have displayed months with greater and less than $40000 salary.

-- CREATE TABLE
CREATE TABLE PAYSLIP(
    MONTH_NAME DATE,
    SALARY FLOAT
);

-- INSERT DATA
INSERT INTO PAYSLIP
VALUES (DATE '2022-01-01', 43000);

INSERT INTO PAYSLIP
VALUES (DATE '2022-02-01', 41000);

INSERT INTO PAYSLIP
VALUES (DATE '2022-03-01', 39000);

INSERT INTO PAYSLIP
VALUES (DATE '2022-04-01', 45000);

INSERT INTO PAYSLIP
VALUES (DATE '2022-05-01', 46000);

INSERT INTO PAYSLIP
VALUES (DATE '2022-06-01', 41000);

INSERT INTO PAYSLIP
VALUES (DATE '2022-07-01', 39000);

INSERT INTO PAYSLIP
VALUES (DATE '2022-08-01', 40000);

INSERT INTO PAYSLIP
VALUES (DATE '2022-09-01', 45000);

INSERT INTO PAYSLIP
VALUES (DATE '2022-10-01', 44000);

INSERT INTO PAYSLIP
VALUES (DATE '2022-11-01', 45000);

INSERT INTO PAYSLIP
VALUES (DATE '2022-12-01', 41000);

-- VIEW DATA
SELECT * FROM PAYSLIP;

-- EXAMPLE 1: VIEW DATA USNG TO_CHAR
SELECT TO_CHAR(MONTH_NAME, 'Month dd, YYYY') 
AS "DATES SAL ABOVE $40000"
FROM PAYSLIP
WHERE SALARY >= 40000;

-- EXAMPLE 2: VIEW DATA USING TO_CHAR
SELECT TO_CHAR(MONTH_NAME, 'Month dd, YYYY') 
AS "DATES SAL LESS THAN $40000"
FROM PAYSLIP
WHERE SALARY <= 40000;

Output:

In this output, the result of the query is inside the red box. The information is fetched from the oracle database wherein the date is printed where the salary is less than and more than $40,000 US dollars.

Read: Number Datatype in Oracle Database

Oracle to_char date format month

The calendar has 12 months in a year starting from January to December. The month names are written and pronounced in different ways across the countries.

In this section, we will learn how to fetch month names from the date using the oracle to_date format. Also, we will share an example to demonstrate the same.

• Month can be displayed in 3 ways in oracle database:
  • (1 – 12) : In numeric between 1 to 12
  • Mon : Abbreviation by displaying first few letters of month.
  • Month: Full name of the month.
• MM displays the name of the month in numeric. If month is january then MM will display 01.
• MON displays abbreviated name of month. If month is Jauary then it will display JAN.
• MONTH displays full name of the month.
• Month name is displayed the way format is declared. For example, if format is MON (all in upper case) then three letters of month will be displayed in upper case.
• To change the day language, add another parameter NLS_DATE_LANGUAGE=<language_name>.

Syntax:

TO_CHAR(DATETIME, 'format', NLS_DATE_LANGUAGE=<LANGUAGE_NAME>)

Example:

The below script demonstrates, the fetching of the name of the month from the system date using to_char, and since today is Monday 24 Jan 2021 the information is displayed accordingly.

SELECT 
    TO_CHAR(SYSDATE, 'MM') as "Month",
    TO_CHAR(SYSDATE, 'MON') AS "MONTH",
    TO_CHAR(SYSDATE, 'MONTH') AS "MONTH",
    TO_CHAR(SYSDATE, 'Mon') AS "Month",
    TO_CHAR(SYSDATE, 'mon') AS "month",
    TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE=SPANISH') AS "MONTH IN SPANISH"
FROM DUAL;

Output:
In this output, the month name is displayed the way its format is provided in the above script. If MONTH is in upper case then the result is also in uppercase and so on.

Read: How to create table in Oracle

Oracle to_char date format day of week

In this section, we will learn how to fetch the day of the week from date using the to_char date format in the oracle database.

• Each week has 7 days, and this information can be displayed in 3 ways:
  • (1 – 7) : In numeric between 1 to 7
  • DY : Abbreviation by displaying first few letters of the week day.
  • DAY: Full name of the day of week.
• D displays day of the week in numeric. If it’s monday then D will return 02.
• DY displays abbrevated day of the week. If it’s monday then DY will return MON.
• DAY displays full name of the day of the week.
• Please note that DY will display abbrevated day of week in upper case, dy will display day of week in lower case, Dy will display day of week with first letter as capital. Same goes with the DAY, Day, day.
• Sunday is the first day of the week and Saturday is the last day of the week on oracle database 19c & 21c.
• To change the day language, add another parameter NLS_DATE_LANGUAGE=<language_name>.

Syntax:

TO_CHAR(DATETIME, 'format', NLS_DATE_LANGUAGE=<LANGUAGE_NAME>)

Example:

The below script demonstrates, the fetching of a day of the week from the system date using to_char, and since today is Monday 24 Jan 2021 the information is displayed accordingly.

SELECT 
    TO_CHAR(SYSDATE, 'D')AS "DAY OF WEEK",
    TO_CHAR(SYSDATE, 'DY') AS "DAY OF WEEK",
    TO_CHAR(SYSDATE, 'DAY') AS "DAY OF WEEK",
    TO_CHAR(SYSDATE, 'Dy') AS "Day of week",
    TO_CHAR(SYSDATE, 'Day') AS "Day of Week",
    TO_CHAR(SYSDATE, 'day') AS "day of week"
FROM DUAL;

Output:

In this output, the day of the week is displayed the way its format is provided in the above script. If DAY is in upper case then the result is also in uppercase and so on.

Read: Oracle Database vs MySQL Database

Oracle to_char date format timezone

Oracle date format does not allow timezone so you cannot perform the to_char() method directly on the date. But the to_char() method can be performed on Timestamp and New_Time() method to work with timezone.

Oracle to_char date format timezone using Timestamp

The TIMESTAMP data type is an extension of the DATE datatype. Timestamp allows to store day, month, year, hour, minute, and second, microseconds, milliseconds values inside the oracle database.

In the below example, SYSTIMESTAMP is used to fetch dates and times from the system. TZH: TZH is used to fetch the timezone information in the oracle database.

SELECT TO_CHAR (SYSTIMESTAMP, 'DD.Mon.YYYY HH:MI:SS.FF TZH:TZM') 
AS RESULT
FROM DUAL;

In the output, the date and time are displayed with the timezone in oracle database 19c & 21c.

Read: Oracle to_date Format [Complete Guide]

Oracle to_char date format timezone using New_Time

NEW_TIME in the oracle database returns the date-time in the time zone when the date-time in the other time zone is a date.

Set the NLS_DATE_FORMAT parameter to display 24-hour time before using this function. New_Time always returns DATE, regardless of the datatype of date.

Below is the list of timezones that you can use in New_Time in the oracle database:

TIMEZONE	Full-Form
AST, ADT	Atlantic Standard or Daylight Time
BST, BDT	Bering Standard or Daylight Time
CST, CDT	Central Standard or Daylight Time
EST, EDT	Eastern Standard or Daylight Time
GMT, GMT	Greenwich Mean Time
HST, HDT	Alaska-Hawaii Standard Time or Daylight Time.
MST, MDT	Mountain Standard or Daylight Time
NST, NST	Newfoundland Standard Time
PST, PDT	Pacific Standard or Daylight Time
YST, YDT	Yukon Standard or Daylight Time

Example:

In our example, we have demonstrated the oracle to_date date format timezone using new_time in the oracle database. For a better explanation, we have used multiple timezones.

Script:

SELECT TO_CHAR(
  NEW_TIME(SYSDATE, 'GMT', 'GMT'),
  'DD-MON-YY HH24:MI:SS'
  ) AS "Greenwich Mean Time"
FROM DUAL;

SELECT TO_CHAR(
  NEW_TIME(SYSDATE, 'BST', 'BDT'),
  'DD-MON-YY HH24:MI:SS'
  ) AS "Bering Standard or Daylight Time"
FROM DUAL;

SELECT TO_CHAR(
  NEW_TIME(SYSDATE, 'YST', 'YDT'),
  'DD-MON-YY HH24:MI:SS'
  ) AS "Yukon Standard or Daylight Time"
FROM DUAL;

SELECT TO_CHAR(
  NEW_TIME(SYSDATE, 'MST', 'MDT'),
  'DD-MON-YY HH24:MI:SS'
  ) AS "MOUNTAIN STANDARD OR DAYLIGHT TIME"
FROM DUAL;

Output:

In this output, the red marked text is the output of the above query and yellow text shows the timezone being used.

Read: Alter Table Add Column Oracle

Oracle to_char date format examples

In our example, we have created a database of a US-based firm ‘FRESHBREAD‘. In this, we have created popular columns like product ID, name, mfd, expiry date of the product, etc.

Using Oracle to_char format, we have displayed the manufacturing and expiration date of the product. The output is quoted in a statement.

-- CREATE TABLE
CREATE TABLE FRESHBREAD(
    PRODUCT_ID INTEGER PRIMARY KEY,
    PRODUCT_NAME VARCHAR2(100),
    PRODUCT_DESCRIPTION VARCHAR2(500),
    MANUFACTURED_DATE DATE,
    EXPIRY_DATE DATE,
    PRICE FLOAT
);

-- INSERT DATA
INSERT INTO FRESHBREAD
VALUES(101,'Ciabatta', 'AMERICANS FAVIORITE Cheesy French Onion Bread',DATE '2022-01-01', DATE '2022-01-06',18.00);

INSERT INTO FRESHBREAD
VALUES(102,'Whole Wheat Bread', 'HEALTHY AMERICANS LOVE THIS',DATE '2022-01-02', DATE '2022-01-07',25.00);

INSERT INTO FRESHBREAD
VALUES(103,'Sourdough', 'YEAST BASED BREAD',DATE '2022-01-01', DATE '2022-01-08',15.00);    

INSERT INTO FRESHBREAD
VALUES(104,'Rye Bread', 'BREAD WITH DILL SEEDS',DATE '2022-01-02', DATE '2022-01-05',15.00);   

-- VIEW DATA
SELECT * FROM FRESHBREAD;

-- FETCH RELEVANT INFO USING TO_CHAR
SELECT 'Manufactured date of '||PRODUCT_NAME || ' is' || TO_CHAR(MANUFACTURED_DATE, ' MON DD, YYYY')
|| ' and it will expire on' || TO_CHAR(EXPIRY_DATE, 'MON DD, YYYY') AS "PRODUCT INFORMATION"
FROM FRESHBREAD;

Output:

The red marked area is the output of the query. In this output, a message is printed with the manufacturing and expiration date of the product. Using the to_char format, we have displayed the date in a string format.

You may also like to read the following tutorials on the Oracle database.

• Oracle Add Foreign Key
• Oracle Add Primary Key
• Oracle how to copy a table
• Oracle stored procedure create table

So, in this tutorial, we have learned about the oracle to_char date format. Also, we have covered these topics:

• Oracle to_char date format
• Oracle to_char date format options
• Oracle to_char date format milliseconds
• Oracle to_char date format microseconds
• Oracle to_char date format am/pm
• Oracle to_char date format in where clause
• Oracle to_char date format month
• Oracle to_char date month name
• Oracle to_char date format day of week
• Oracle to_char date format timezone
• Oracle to_char date format examples