TO_ DATE
Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
On this page
Converts a string to a DATE
or DATETIME
value.
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_
may contain characters that are not format specifiers.from_ format These characters must appear in the same position in convert_
; otherwise,from_ value TO_
will returnDATE NULL
. -
For failed conversions, the function throws an error.
-
In
convert_
andfrom_ value convert_
values, all punctuations can be used as separators ( ! " # $ % & ’ ( ) * + , - .from_ format / : ; < = > ? @ [ \ ] ^ _ ` { ¦ } ~ ). The separator in convert_
may not match the seperator infrom_ value 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_
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_
, 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_
, 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_
, where the string to be converted contains the spelling of the month.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 |
+------------+
Last modified: May 31, 2023