TO_DATE

Syntax

TO_DATE ( convert_from_value, convert_from_format )

Arguments

• convert_from_value: A string specifying the value to convert from.

• convert_from_format: A format string, comprised of one or more format specifiers.

Remarks

• convert_from_format may contain characters that are not format specifiers. These characters must appear in the same position in convert_from_value; otherwise, TO_DATE will return NULL.

• For failed conversions, the function throws an error.

• In convert_from_value and convert_from_format values, all punctuations can be used as separators ( ! " # $ % & ’ ( ) * + , - . / : ; < = > ? @ [ \ ] ^ _ ` { ¦ } ~ ). The separator in convert_from_value may not match the seperator in convert_from_format.

• The function automatically fills the missing date parameters. The missing parameters take the following values:

  • day: first day of the month

  • month: the current month

  • year: the current year

• For DATETIME value conversion, fractional seconds are not supported.

Examples

Example 1

The following examples demonstrate a typical call to TO_DATE where the string to be converted is specified in MM/DD/YYYY and MM/DD/YYYY HH:MI:SS formats.

SELECT TO_DATE('03/01/2019','MM/DD/YYYY') AS result;

+------------+
| result     |
+------------+
| 2019-03-01 |
+------------+

SELECT TO_DATE('03/01/2019 10:40:27','MM/DD/YYYY HH:MI:SS') AS result;

+---------------------+
| result              |
+---------------------+
| 2019-03-01 10:40:27 |
+---------------------+
1 row in set (0.00 sec)

Example 2

If day is passed as the string in TO_DATE, it returns the current month and year in YYYY/MM/DD format.

The following is the output when the command is run in April 2019.

SELECT TO_DATE('30','DD');

+--------------------+
| TO_DATE('30','DD') |
+--------------------+
| 2019-04-30         |
+--------------------+
1 row in set (0.41 sec)

If year is passed as the string in TO DATE, it returns the specified year, current month and first day of the month:

The following is the output when the command is run in the month of April.

SELECT TO_DATE('2019','YYYY');

+------------------------+
| TO_DATE('2019','YYYY') |
+------------------------+
| 2019-04-01             |
+------------------------+
1 row in set (0.00 sec)

If month is passed as the string in TO_DATE, it returns first day of the specified month of the current year:

The following is the output when the command is run in the year 2019.

SELECT TO_DATE('11','MM');

+--------------------+
| TO_DATE('11','MM') |
+--------------------+
| 2019-11-01         |
+--------------------+
1 row in set (0.23 sec)

Example 3

The following example demonstrates another call to TO_DATE, where the string to be converted contains the spelling of the month. In this example, the string The day is is included in the same location in both arguments, allowing the function to successfully return a DATE value.

SELECT TO_DATE('The day is March 01, 2019','The day is MONTH DD, YYYY') AS Result;

+------------+
| Result     |
+------------+
| 2019-03-01 |
+------------+