EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Oracle Tutorial Oracle to_date
Secondary Sidebar
Oracle Tutorial
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Golden Gate
    • Oracle Virtual Machine
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle?Primavera
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle ERP
    • Oracle ASM
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Oracle to_date

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.

Syntax

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (85,992 ratings)

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
Popular Course in this category
Oracle Training (14 Courses, 8+ Projects)
  14 Online Courses |  8 Hands-on Projects |  120+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Oracle DBA Database Management System Training (2 Courses)4.9
All in One Financial Analyst Bundle- 250+ Courses, 40+ Projects4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more