Oracle to_date Format [Complete Guide]

by
Oracle to_date format 24 hours

In this Oracle tutorial, we will discuss the oracle to_date format function. Using the to_date format, you can convert the char, varhcar2, nchar, or any other string format to the Date format. Furthermore, we will also cover these topics.

  • Oracle to_date format
  • Oracle to_date format sample
  • Oracle to_date format syntax
  • Oracle to_date format 24 hours
  • Oracle to_date format with time
  • Oracle to_date format without time
  • Oracle to_date format am pm
  • Oracle to_date format milliseconds
  • Oracle to_date format mm/dd/yyyy
  • Oracle to_date format code appears twice
  • Oracle to_date format timezone
  • Oracle to_date format mask
  • Oracle to_date format date and time
  • Oracle to_date equivalent in SQL server
  • Oracle to_date format examples

Oracle to_date format

Oracle to_date format converts the date from string data type (char, varchar2, etc) to date data type.

  • The user provides input in a formatted text and to_date format converts that text expression to Datetime.
  • In this way, record is saved in a datetime format inside the database which later helps filtering data based upon date and time.
  • There are many uses of DateTime format in the database also it solves various problems.
  • For example, logs are saved with date and time in the database. Incase of any illegal activity logs can be checked & based upon the date and time correspoding user can be questioned about the incident.
  • Oracle provides various methods to perform actions using datetime values. If date and time is entered in string format then those methods can’t be applied.

Also, check: How to Check Oracle Database Version

Oracle to_date format syntax

Here is the syntax of to_date format in oracle 19c. String-text must be a date format, do not mention any name or other strings otherwise, it will throw an error.

TO_DATE(string-text, [fmt,] [option setting])
  • string-text is the date in the string data type. It must have day, month, year information apart from that you can also provide time in the string-text.
  • fmt is the format for date, string-text must be according to the format. fmt is based upon the default NLS_DATE_FORMAT.
  • Option setting include the the NLS_DATE_LANGUAGE. Each country has different ways of writing the date format for example:
    • United States America – month/date/year
    • United Kingdom – date/month/year
    • Japan – year-month-date
    • Switzerland: date/month/year
  • You can specify the date format on the basis of country using this command – NLS_DATE_LANGUAGE = 'American'.
  • NLS formats, languages and territories information can be found on this page.

Oracle to_date format sample

In this section, we have shared the oracle to_date format sample so that you can understand how it works. In our sample, we have converted the International women’s day date from string to date format using the to_date function in oracle 19c.

               -- String-text      fmt
SELECT to_date('08 March 1995','DD-MM-YYYY') As "Womens_Day"
FROM DUAL;

In this output, 08-MAR-95 is the date format created using ‘08 March 1995‘ that was a text sequence.

Womens_Day
----------
08-MAR-95

Read: Connect Excel to Oracle Database

Oracle to_date format 24 hours

In the previous section, we have learned about oracle to_date format, syntax, and sample. Now in this section, we will cover how to set the oracle to_date format to 24 hours.

  • Although, the name ‘TO_DATE’ sounds like everything is related to date but you can define time as well in oracle database 19c.
  • Add time information immediately after providing the date format in to_date function.
  • You can provide time in the following format HH:MI:SS in oracle database 19c.
    • HH – Hours
    • MI – Minutes
    • SS – Seconds
  • HH:MI:SS in oracle database shows the time format based upon 12 hours. Adding 24 to HH will change it to 24 hours format HH24:MI:SS.
  • In our example, we have demonstrated use of both 12 hours and 24 hours format using to_date function in oracle database 19c.
  • Oracle to_date format 24 hours can be performed in two ways:
    • Oracle to_date format 24 hours using SQLPLUS (command line)
    • Oracle to_date format 24 hours using SQL Developer Tool (GUI)

Oracle to_date format 24 hours using SQLPLUS (command line)

If you are using sqlplus or any other command-line tool, then this tutorial will help you in oracle to_date format 24 hours.

The first, thing is the process is setting up the date format for oracle database 19c. Type the below command on the top. By default, the date format doesn’t display time. The below command will enable the display of time in oracle 19c.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR HH:MI:SS';

Start typing the below script on the sqlplus or other command-line tool. We have tested these scripts well, still, in case of any error please write us with the exact error message.

Script for oracle to_date format 12 hours:

In this oracle script, a time format of 12 hours is provided. The program will accept time between 1 to 12 hours. For demonstration purposes, we have provided the wrong value i.e. 13 in the second part of the same program. 

-- 12 hours time format

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR HH:MI:SS';

SELECT TO_DATE(
    'JULY 04 1776 12:01:00',
    'MM/DD/YYYY HH:MI:SS', 
    'NLS_DATE_LANGUAGE = AMERICAN'
) As Republic_Day
FROM DUAL;

-- provided 13 instead of 1 [incorrect input]
SELECT TO_DATE(
    'JULY 04 1776 13:01:00',
    'MM/DD/YYYY HH:MI:SS', 
    'NLS_DATE_LANGUAGE = AMERICAN'
) As Republic_Day
FROM DUAL;

Output:

The output shows the implementation of the oracle to_date format for 12 hours. In this output, the same program is executed twice, the first time with the correct value and the next time with the incorrect value.

Line 2 in both the queries shows the time input and line 3 displays the time format. The default time is always 12 hours.

Oracle to_date format for 12 hours
Oracle to_date format for 12 hours

Script for oracle to_date format 24 hours:

In this oracle script, a time format of 24 hours is provided. The program will accept time between 1 to 24 hours. 

-- 24 hours time format

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR HH24:MI:SS';

SELECT TO_DATE(
    'JULY 04 1776 13:01:00',
    'MM/DD/YYYY HH24:MI:SS', 
    'NLS_DATE_LANGUAGE = AMERICAN'
) As Republic_Day
FROM DUAL;

Output:

The output shows, implementation of oracle to_date format 24 hours. In line 2 we have provided input as 13 and the program has accepted it. In line 3, HH24 shows that format of the time is set to 24 hours.

Oracle to_date format 24 hours
Oracle to_date format 24 hours

Oracle to_date format 24 hours using SQL Developer Tool (GUI)

SQL developer is a great tool for performing huge oracle queries. It allows both command-based and GUI-based operations. In this section, we will learn about oracle to_date format 24 hours using SQL developer tool with a graphical user interface.

By default, to_date() function do not displays time in oracle database 19c. You can change the date format from Tools -> Preferences -> NLS -> set date format to: DD-MON-RR HH24:MI:SS A.M. from DD-MON-RR.

oracle to_date format sql developer
oracle to_date format SQL developer

Now, run the to_date query to convert the string format of date to date values with a date data type. Also, mention HH24 in fmt (format). Below is the sample code to display Oracle to_date format 24 hours.

SELECT TO_DATE(
    'JULY 04 1776 13:01:00',
    'MM/DD/YYYY HH24:MI:SS', 
    'NLS_DATE_LANGUAGE = AMERICAN'
) As Republic_Day
FROM DUAL;

Read: How to Fetch Data from Oracle Database in Python

Oracle to_date format with time

Oracle to_date format converts the char and other string formats to date and time value. Users can fill in the date, time, and meridian information in the oracle database 19c.

Beginner struggles to work with time in oracle database while using to_date function. In this section, we will cover the solution for frequently asked questions – oracle to_date not showing time.

  • Most of the methods related to date and time in oracle is supported by NLS.
  • NLS provides Language, format, territory, etc support related to date and time in oracle database 19c.
  • You can select format, language, territory, etc of your choice by altering the NLS date parameter. This may sound complex, but after reading the blog it will become easy for you.
  • NLS format can be changed using NLS_DATE_FORMAT = [name of the format] command. This setting can be changed from both command line and GUI interface.
    • Oracle to_date format with time using sqlplus (command-line)
    • Oracle to_date format with time using sql developer tool (GUI)

Oracle to_date format with time using sqlplus (command-line)

If you are working with sqlplus then login to the user of your choice. In our case, we are logged in as sys users. The below code will change the default date format (DD-MON-RR) to date and time format (DD-MON-RR HH24:MI:SS).

You can customize DD-MON-RR HH24:MI:SS format as per your requirements. You can also add AM and PM to this format. HH24 here shows that users can provide time in 24 hours format. Remove 24 from HH to change the time format to 12 hours.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR HH24:MI:SS';

This will solve the issue related to oracle to_date not showing time. Please follow the above section – Oracle to_date format 24 hours to learn about Oracle to_date format with time.

Note: All the settings will be restored once the sqlplus is closed it not committed.

Oracle to_date format with time using sql developer tool (GUI)

It is important to alter the date format settings under NLS to show time in the oracle to_date function. Follow these steps to change the settings as per your/business needs.

By default, to_date() function do not displays time in oracle database 19c. You can change the date format from Tools -> Preferences -> NLS -> set date format to: DD-MON-RR HH24:MI:SS A.M. from DD-MON-RR.

oracle to_date format sql developer
oracle to_date format SQL developer

Read: How to create a database in Oracle 19c

Oracle to_date format without time

If you have a requirement to convert the string characters to date format without time then in this section, we have explained about oracle to_date format without time.

  • In the above sections, we have explained how to use to_date format to convert date and time string data types into date datatype.
  • In the to_date() function, pass the string statement and format (fmt) without mentioning the date. This way you can use oracle ro_date format without time.
SELECT TO_DATE('4 July 1776','dd Month YYYY')
AS "REPUBLIC DAY IN USA"
FROM DUAL;

In this output, time is not mentioned because we have not passed it in the above script. The output shows the date as 04-JUL-76 which is celebrated as Republic Day in the United States of America (USA) since 1976.

REPUBLIC DAY IN USA
-------------------
04-JUL-76

Read: Oracle Database vs MySQL Database

Oracle to_date format am pm

Ante meridiem (a.m.) and Post meridiem (p.m.) can be assigned to time using to_date format in oracle database 19c.

  • Alter the information at NLS_DATE_FORMAT and add A.M. or P.M. at the end of the time format.
  • This will automatically display the meridiem time as per the time provided time in oracle database 19c.

Script for oracle to_date format am pm:

Use this script on the SQL developer tool’s worksheet. In this query, we have displayed USA_Republic_Day date with time and meridiem as A.M.

SELECT TO_DATE(
    'JULY 04 1776 01:01:00 AM',
    'MM/DD/YYYY HH:MI:SS AM', 
    'NLS_DATE_LANGUAGE = AMERICAN'
) As USA_Republic_Day
FROM DUAL;

Output:

In this output, Date is displayed with Post meridiem (AM) in the SQL developer tool.

Oracle to_date format am pm meridiem
Oracle to_date format am pm meridiem

Read: How to create table in Oracle

Oracle to_date format milliseconds

As per the official documentation of oracle, oracle supports YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. Use timestamp instead of date to demonstrate the milliseconds in oracle database 19c.

Oracle to_date format mm/dd/yyyy

Date include month, day, and year but this sequence is not followed same in all countries. Even the biggest countries like the United States of America (USA) and The United Kingdom of Great Britain follows different date formats.

So in this section, we will learn about how to set date format as mm/dd/yyyy using the to_date function in oracle database 19c.

  • In the United States of America, dates are traditionally written in the “month-day-year”, which is different from most of the countries.
  • The format can be changed from hyphens(-) to slashes(/) using alter session command on NLS_DATE_FORMAT in oracle database 19c.
  • In the below example, we have displayed code for viewing current date format, how to change the date format, and in the end there is an implementation.
  • In the script, we have displayed date of Republic day in United States of America (USA) i.e. July 04 1776 in MM/DD/YYYY format in oracle database 19c.

Script:

-- View current date format
SELECT value
FROM V$NLS_PARAMETERS
WHERE parameter = 'NLS_DATE_FORMAT';

-- Change the format  
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY';

-- script to display date in MM/DD/YYYY format
SELECT TO_DATE(
    'JULY 04 1776',
    'MM/DD/YYYY',
    'NLS_DATE_LANGUAGE = AMERICAN'
) As Republic_Day
FROM DUAL;

Red marking shows the use of the above script, below each red marked script there is an output for that query. In the end, the republic date is displayed in mm/dd/yyyy format in oracle database 19c.

Oracle to_date format mm/dd/yyyy format
Oracle to_date format mm/dd/yyyy example

Read: Number Datatype in Oracle Database

Oracle to_date format code appears twice

While executing the oracle to_date function if you have encountered the below error that means you have repeated the format.

ERROR at line 1:
ORA-01810: format code appears twice

Most of the time people miss-spell the minute’s format as MM instead of MI. In that case, an exception is raised as now the program has two values as MM.
MM is used for a month in the date format and MI is used for Minutes in time format. To solve the above error message, replace the MM used in the time format with MI in oracle database 19c.

If the issue persists, pay attention to the rest of the code and if not solved please write us with the script snippet.

Read: Alter Table Add Column Oracle

Oracle to_date format time zone

A time zone is an area that follows the same standard time for all purposes. Time zones follow country boundaries and their subdivisions.

All the time zones are defined as offsets (whole number of hours) from Coordinated Universal Time (UTC), ranging from UTC-12:00 to UTC+14:00.

Time zone in oracle can be used using the timestamp function. In the to_date function, we cannot provide the time zone as per the NLS_DATE_FORMAT.

Read: Oracle add primary key

Oracle to_date format mask

Countries follow the different formats of writing the date but oracle has a standard format in which date can be put inside the database. So, to fill this gap to_date function is used which converts any format into an oracle understandable format.

Below is the sample of writing date 04 July, 1776 into different formats. 

04 July, 1776
July 04, 1776
04/07/1776
07/04/1776
04-07-1776
07-04-1776
1776-07-04

There are many more ways of writing the date but in all cases, we are getting the date of Republic Day in the United States of America (USA).

So it was hard to feed in all the formats for the oracle to accept as date so oracle created the to_date function using which users of any country can mask the date format as per their region.

Script:

In this script, we have masked the date using the to_date function. The bold text shows the output and each output date is appearing in a different format.


ALTER SESSION SET NLS_DATE_FORMAT = 'DD MON, RRRR HH:MI:SS';

SELECT TO_DATE('04 July, 1776 01:01:00') 
As RepublicDay FROM DUAL;

REPUBLICDAY
---------------------
04 JUL, 1776 01:01:00

ALTER SESSION SET NLS_DATE_FORMAT = 'MON DD, RRRR HH:MI:SS';

SELECT TO_DATE('July 04, 1776 01:01:00') 
As RepublicDay FROM DUAL;

REPUBLICDAY
---------------------
JUL 04, 1776 01:01:00

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/RRRR HH:MI:SS';

SELECT TO_DATE('July 04, 1776 01:01:00') 
As RepublicDay FROM DUAL;

REPUBLICDAY
-------------------
07/04/1776 01:01:00

Read: Oracle Add a Column to Table

Oracle to_date format date and time

Oracle to_date function is used to convert the date values in character to date data type. Also, it helps in storing the date in the oracle database.

In the below example, we have demonstrated oracle to_date format date and time. We have inserted the string text, followed by the date-time format. 

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR HH:MI:SS';

Select TO_DATE('04-07-1776 01:01:00', 'DD-MM-YY HH:MI:SS') 
As RepublicDay
 
FROM dual;

Here is the output of the above script, this date is displayed in the DD-MON-YY HH:MI:SS the format which is the standard format of oracle database 19c.

REPUBLICDAY
------------------
04-JUL-76 01:01:00

Read: Oracle get database name

Oracle to_date equivalent in SQL server

SQL Server does not have to_date function but using convert() function in SQL server, the string can be converted to date. At some places cast() function can also satisfy the requirement of conversion of string to date.

In the below script, we are converting the string formatted date to date data type.

Select Convert(Datetime, '1776-07-04');

Read related tutorials.

In this tutorial, we have learned about the oracle to_date format. Also, we have covered these topics:

  • Oracle to_date format
  • Oracle to_date format sample
  • Oracle to_date format syntax
  • Oracle to_date format 24 hours
  • Oracle to_date format with time
  • Oracle to_date format without time
  • Oracle to_date format am pm
  • Oracle to_date format milliseconds
  • Oracle to_date format mm/dd/yyyy
  • Oracle to_date format code appears twice
  • Oracle to_date format timezone
  • Oracle to_date format mask
  • Oracle to_date format date and time
  • Oracle to_date equivalent in SQL server
  • Oracle to_date format examples