EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials Oracle Tutorial Oracle to_date
 

Oracle to_date

Updated May 12, 2023

Oracle to_date

 

 

Introduction to Oracle TO_DATE function

Likewise in SQL, the to_date function in Oracle, a widely used string conversion technique used by database developers and administrators basically to convert a character value to date (DATETIME) datatype.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax

It has a pretty simple one liner syntax and easy to remember:

to_date (string, format)

We can see some arguments above in the function:

  • string

This is the value that is converted into a DATE value. The string may be a value of any data type like CHAR etc.

  • format

The format is basically the date and time format for the string which will be considered as the raw date type.

The format argument can be ignored as a parameter as the string can be in the standard date format as well which is dd-mon-yy. e.g., 20-jan-2020.

How TO_DATE function works in Oracle?

Oracle is one of the largest database supporting many data types and formats. Date and Time formats are supported in oracle but are someway different than other standard SQL databases. The date type is not dfferent in approach than other types, but its syntax is little bit different which we’ll check below and understand how does it works. There are lot of types of strings which can be converted into date value. But here, we are specifically dealing with the dates in string. For string conversion, we need the Oracle date format elements like year and month. Below are some formats used as parameters. The syntax is a one liner query which is pretty simpler and can be used anytime to operate on dates. Any string with dates in it can be converted into date value and stored in the database.

Let us take the below date literal to illustrate and understand more how the function works in real time:

‘7 May 2019’

This is a date but as a string and we need to convert into proper DATE format. For the conversion of the string into DATE format, we must specify a datetime string and its exact format which is shown in the following example as below::

Select to_date( '7 May 2019', 'dd mon yyyy' ) from dual

The output is as follows:

Oracle to_date output 1

Since it is based out of Oracle, we don’t need semicolon at the end of query. But in case we forgot to specify the datetime format or miss a character in the argument, then we will get syntax error.

Examples

Let us check out few examples to examine how to_date function actually works in real time.

  • Let us take a simple example where we have ddmmyyyy as string. It can be converted into a proper date format as shown in the below query:

Select to_date('16092017','ddmmyyyy') from dual

Oracle to_date output 2

Select to_date('8-09-17','mm-dd-yy') from dual

Here, the string is in MM-DD-YY format which is converted into date value as shown below:

Oracle to_date output 3

Select to_date('Dec 16','mon yy') from dual

This example shows string in MON YY format. Let’s see the output below:

output 4

Select to_date('11/09/23','yy/mm/dd') from dual

The above format is not so common but the best is to convert it as a date value and store in the database. The result can be seen below:

output 5

Let us check few more strings in different formats and change it to date value.

  • Now lets check an example where we have time in hours and minutes along with date.

Select to_date('07-11-2020 09:24',’mm-dd-yyyy hh:mi') from dual

output 6

Doesn’t matter what the string comprises of. The result will always be in date format.

There can be other examples where we can store data in a table wherein we can store date in proper date format.

Note:

  • A timestamp in string format to to_date can be used; Then the value will be converted into a date. Note that a time string cannot be translated to a date value.
  • Also, in examples above we used lowercase year (e.g. yyyy) and day (e.g. dd) formats; not uppercase (e.g. YYYY or DD) formats. Its easy but confusing sometimes.
  • The uppercase YYYY format is not commonly used but specifies week year that means we must also specify a week-of-week-based-year (w) format value.
  • Similarly, the uppercase D format, also uncommon, specifies day-of-the-year, not the day of the month.

Rules and Regulations

While executing the to_date query we must keep in mind some important points. Some can be considered below:

  • Note that the format parameter in the syntax is optional. If the format is not specified in the input then the string needs to be written in the standard date format which is dd-mm-yyyy.

Eg:  11-jan-2005

  • Point to note here is that the input characters should be in string format and not in number format.
  • Format model should be enclosed in single quotation marks and is case sensitive.
  • The format model can contain any valid date format element. Be sure to distinguish the date value and the format model by a comma.
  • Days and months names in the result are automatically padded with blanks.
  • We can remove the padded blanks or suppress leading zeros, using the fill mode fm element.
  • We can resize the display width of the output character fıeld with the SQL*Plus COLUMN command.
  • Width of the resultant column is 80 characters by default.

The following table below illustrates the time parameter used in the TO_DATE function.

Parameter Explanation

As to_date function takes date parameters to convert it as a date, we may consider checking few parameters below for our usage.

HH                        12 hour format

HH12                   12 hour format

HH24                   24 hour format

MI                        0 to 59 minutes

SS                         0 to 59 seconds

SSSS                     0 to 86399 seconds

FF                         Fractional seconds

Normally, date can be seen as string in raw tabular data. After importing the data, we can consider changing it into date format.

Conclusion – Oracle to_date

Oracle TO_DATE is widely used function and acts a perfect parameter for quick conversion for storing data in database. Every DBA, Analyst or Developer who works on SQL or Database can use this function to play around with dates and maintain a proper date format and store it in the database to retrive easily.

Recommended Articles

This is a guide to Oracle to_date. Here we discuss How TO_DATE function works in Oracle along with the Examples and Parameters. You may also have a look at the following articles to learn more –

  1. Oracle INSTR()
  2. Oracle Index
  3. Oracle SYSDATE()
  4. Oracle COALESCE

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW