How do I Convert String to Date in Oracle?

Written by sara wayne
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How do I Convert String to Date in Oracle?
Use the "to_date" SQL command to convert a string to a date. (old calendar image by Allyson Ricketts from

Oracle uses the date format to store both date and time in a single combined column that includes month, day, year, hour, minute, second and millisecond. Dates only compare to other dates successfully. This rule makes it necessary to convert strings within sequential files to the date format before comparing them to a date stored in an Oracle table. Convert the string to a date with a simple structured query language (SQL) command.

Skill level:

Other People Are Reading

Things you need

  • Oracle database
  • Interactive query tool

Show MoreHide


  1. 1

    Read a date value from an input file. For this example, assume that the value is "20100101".

  2. 2

    Use the "to_date" function to convert the string to a date. The format is

    to_date(string 1, [format_mask]).

    String1 is the string value, and format_mask defines the output format.

    In this example,

    to_date('20100101', 'yyyymmdd')

    returns "2010/01/01".

  3. 3

    Oracle uses the to_date function with the update and insert commands without requiring the user to explicitly coding the statement. The two statements shown below function exactly the same. The first one is an example of implicit coding, and the second is an example of explicit coding.

    insert into date_table values ('date 1', '20100101');

    insert into date_table values ('date 2', to_date('20100101', 'yyyymmdd');

Tips and warnings

  • The format mask allows the user to define the date format in many different configurations.
  • MM stands for numeric month.
  • MON stands for the abbreviated month name.
  • MONTH stands for the full month name.
  • DD stands for the day of the month.
  • DY stands for the abbreviated day name.
  • YYYY stands for the four digit year.
  • YY stands for the two digit year.
  • HH stands for hour of the day.
  • HH24 stands for the military hour of the day.
  • MI stands for the minute.
  • SS stands for the second.
  • AM or PM stands for the Meridian indicator.
  • If the string value is not a valid date, the to_date function returns an error message when the SQL statement is executed.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.