Oracle TO_DATE function can convert a string into a datetime value which is acceptable for computing or comparing values of datetime type. In other words, TO_DATE recognizes and converts a string into a datatime value.
In this post, I will introduce Oracle TO_DATE, SQL function in two parts:
Oracle TO_DATE Syntax
There're 4 input arguments in Oracle TO_DATE function to format the syntax.
- Datetime string (char)
- Default value for conversion error (return_value)
- Datetime format (fmt)
- Additional NLS setting (nlsparams)
Datetime string (char)
This is the main input variable of TO_DATE function that you shall use. The format of input value should either match environment datetime language or fmt variable in the third argument of SQL TO_DATE function.
This parameter is mandatory.
Default value for conversion error (return_value)
Since release 12.2, it falls back to this value for any conversion error. This is useful especially when you have less ability to control over the datetime format of an application or environment.
This parameter is optional.
Datetime format (fmt)
Explicit datetime format defined by Oracle helps TO_DATE function to recognize and match the input datetime string.
Although this parameter is optional in SQL TO_DATE function argument list, we usually specify it in practice.
Additional NLS setting (nlsparams)
For recognizing datetime strings in different languages other than the environment language, you can set this value. In other words, if the language of input date string conforms to the environment language, you don't have to set this.
This parameter is optional.
Oracle TO_DATE Examples
How to use TO_DATE function? Here I list 5 usages in this section.
- Basic conversion
- Default value for any conversion error
- Additional NLS setting
- Comparing datetime
- Computing datetime
Basic conversion
We usually use a datetime format in the second argument to tell SQL 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 datetime 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 datetime elements, like YYYY, MM or DD. You can refer to Oracle Database Release 19c : Datetime Format Elements.
Default value for any conversion error
Since 12.2, Oracle adds a fault-tolerant scheme to the function. In the following case, 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
There's no "August 32" in calendar, so the date failed to convert into a normal date, therefore it falls back to the default value which is the first date of 2019. This is what fallback value doing.
Is there any error in the statement? Yes, but the error has been caught and dealt by the fallback (default) statement, it did not throw anything to the front end. So basically, it's not a failed statement, it succeeded.
Additional NLS setting
When your input string expressed with a different language, you have to let SQL 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 an 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.
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.
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. First of all, we change the date format in session.
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.