Conversion Function

Oracle TO_DATE Function Examples

Oracle TO_DATE Function by Examples

Oracle TO_DATE function can convert a date time string into a value acceptable for converting string into datetime type.

There’re 4 major components in Oracle TO_DATE function input arguments.

  1. Date time string (char)
  2. This is the main input variable that you shall use. This is mandatory in TO_DATE function argument list.

  3. Default value for conversion error (return_value)
  4. Since release 12.2, it falls back to this value for any conversion error. This is optional.

  5. Date time format (fmt)
  6. Explicit date time format helps TO_DATE to match the date time string. Although this is optional in TO_DATE function argument list, we usually specify it in practice.

  7. Additional NLS setting (nlsparams)
  8. For any different NLS output, you can set this value. This is optional.

Usage 1: Basic conversion

We usually use a date time format in the second argument to tell TO_DATE function how to deal with the first input argument.

SQL> conn hr/hr
Connected.
SQL> set heading off;
SQL> select TO_DATE('2019-08-30', 'YYYY-MM-DD') from dual;

30-AUG-19

The date time format “YYYY-MM-DD” is very easy to understand, however, it can be more complex just like the following example.

SQL> select TO_DATE('August 30, 2019, 09:35 A.M.', 'Month dd, YYYY, HH:MI A.M.') from dual;

30-AUG-19

For more date time elements, you can refer to Oracle Database Release 19c : Datetime Format Elements.

Usage 2: Default value for any conversion error

I deliberately set a wrong date on August 32th which is not existing in anywhere on earth.

SQL> select TO_DATE('August 32, 2019, 09:35 A.M.' DEFAULT 'January 01, 2019 12:00 A.M.' ON CONVERSION ERROR, 'Month dd, YYYY, HH:MI A.M.') from dual;

01-JAN-19

As we expected, the date failed to convert into a normal date, therefore it falled back to the default value which is the first date of 2019.

Usage 3: Additional NLS setting

When your input string expressed with a different language, you have to let TO_DATE function know what language that you were using. First of all, check current NLS date language.

SQL> select value from nls_session_parameters where parameter = 'NLS_DATE_LANGUAGE';

AMERICAN

Translate Your Input String

In the following example of TO_DATE function, I used a Spanish “Agosto” to indicate the “August”.

SQL> select TO_DATE('Agosto 30, 2019', 'Month dd, YYYY') from dual;
select TO_DATE('Agosto 30, 2019', 'Month dd, YYYY') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

ORA-01843 alerted us that we used an incorrect month for language “AMERICAN”. What should we do? In such moment, we can use additional NLS_DATE_LANGUAGE to tell TO_DATE to treat the input string as Spanish.

For more explanation on this error, you may check: How to Resolve ORA-01843: not a valid month.

SQL> select TO_DATE('Agosto 30, 2019', 'Month dd, YYYY', 'NLS_DATE_LANGUAGE=SPANISH') from dual;

30-AUG-19

Multi-byte Languages

For environments with Asia languages, this additional NLS setting sometimes could be important to use TO_DATE correctly.

For example, in Japanese environment, TO_DATE cannot recognize “August” by default.

SQL> select TO_DATE('August 30, 2019, 09:35 A.M.', 'Month dd, YYYY, HH:MI A.M.') from dual;
select TO_DATE('August 30, 2019, 09:35 A.M.', 'Month dd, YYYY, HH:MI A.M.') from dual
               *
行1でエラーが発生しました。:
ORA-01843: 指定した月が無効です。

ORA-01843: 指定した月が無効です。” notified the user that a unrecognized or invalid month was found. You have to tell TO_DATE what language that you were using.

SQL> select TO_DATE('August 30, 2019, 09:35 A.M.', 'Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE=AMERICAN') from dual;
19-08-30

Otherwise, you have to use a valid month value for Japanese language.

SQL> select TO_DATE('8月 30, 2019, 09:35 午前', 'Month dd, YYYY, HH:MI A.M.') from dual;

19-08-30

Please note that, if you were developing API or software for global distributions, using the NLS option in TO_DATE or TO_CHAR function as a safety net is always a good idea no matter what languages they are using.

Usage 4: Comparing datetime

To tell your input string is larger, smaller or equal, you may use >, < or = to compare two datetime values. To compare three datetime values in a range, you may use BETWEEN AND expression.

In WHERE Clause

You can use TO_DATE to compare with another datetime value in WHERE clause to retrieve rows.

SQL> select count(*) || ' Persons' from employees where hire_date > to_date('2008-03-01', 'YYYY-MM-DD');

4 Persons

SQL> select count(*) || ' Persons' from employees where hire_date between to_date('2007-03-01', 'YYYY-MM-DD') and to_date('2008-03-01', 'YYYY-MM-DD');

22 Persons

Boolean Result

Moreover, we can also use it to return logical result, TRUE or FALSE by leveraging CASE WHEN expression.

SQL> select case when sysdate > TO_DATE('2019-08-30', 'YYYY-MM-DD') then 'TRUE' else 'FALSE' end from dual;

TRUE

SQL> select case when sysdate < TO_DATE('2019-08-30', 'YYYY-MM-DD') then 'TRUE' else 'FALSE' end from dual;

FALSE

Please note that, failed to correctly convert a string into a datetime value before comparing may result ORA-01861: literal does not match format string.

Usage 5: Computing datetime

We can compute on the result of TO_DATE so as to evaluate into another datatime value. Let’s check the following example:

SQL> alter session set nls_date_format='yyyy-mm-dd hh:mi:ss';

Session altered.

SQL> select TO_DATE('August 30, 2019, 09:35 A.M.', 'Month dd, YYYY, HH:MI A.M.') from dual;

2019-08-30 09:35:00

Add Some Hours

Then we add 2 hours of a day (24 hours) to the result of TO_DATE.

SQL> select TO_DATE('August 30, 2019, 09:35 A.M.', 'Month dd, YYYY, HH:MI A.M.') + 2/24 from dual;

2019-08-30 11:35:00

Add Some Years

For adding some years, you can use INTERVAL expression. For example, a company lets employees retire after 30 years of working, we can predict the retire date by adding 30 years to their hire date.

SQL> set heading on;
SQL> column full_name format a20;
SQL> select first_name || ' ' || last_name full_name, hire_date + interval '30' year retire_date from employees order by hire_date;

FULL_NAME            RETIRE_DATE
-------------------- -------------------
Lex De Haan          2031-01-13 12:00:00
Susan Mavris         2032-06-07 12:00:00
Hermann Baer         2032-06-07 12:00:00
...

Please note that, in some occasions, INTERVAL expression may not be as stable as we thought, I recommend you to use Oracle ADD_MONTHS function instead.

Leave a Reply

Your email address will not be published. Required fields are marked *