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

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:

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

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:

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

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

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:

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

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