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.
TO_ TIMESTAMP
On this page
Converts a string to a TIMESTAMP(6)
value.
Syntax
TO_TIMESTAMP ( convert_from_value, convert_from_format )
Arguments
-
convert_
from_ value: String specifying the value to convert from. -
convert_
from_ format: 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_
else, the function will returnfrom_ value NULL
. -
Format specifiers
YYYY
andYY
returns the results inYYYY
format. -
Format specifier
FFn
can accept any value from zero to nine decimal places. -
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 separator infrom_ value convert_
.from_ format
Examples
Example 1
In the following example, the string The date and time are
is included in the same location in both arguments to TO_
, allowing the function to successfully return a TIMESTAMP(6)
value.
SELECT TO_TIMESTAMP('The date and time are 01/01/2018 2:30:15.123456','The date and time are MM/DD/YYYY HH:MI:SS.FF6') AS result;
+----------------------------+
| result |
+----------------------------+
| 2018-01-01 02:30:15.123456 |
+----------------------------+
Example 2
For Year, use either YYYY
, YY
or RR
format specifier.
SELECT TO_TIMESTAMP('10-APR-2018 11:59:59','DD-MON-YYYY HH:MI:SS') AS Result;SELECT TO_TIMESTAMP('10-APR-18 11:59:59','DD-MON-YY HH:MI:SS') AS Result;SELECT TO_TIMESTAMP('10-APR-18 11:59:59','DD-MON-RR HH:MI:SS') AS Result;
+---------------------+
| Result |
+---------------------+
| 2018-04-10 11:59:59 |
+---------------------+
1 row in set (0.24 sec)
Example 3
The following table lists few ways of using format specifier FF
in the TO_
function, along with their output:
Call to |
Result |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
If input fractional is larger than FF format, then it throws an error.
SELECT TO_TIMESTAMP('04-10 11:59:59 123 2018','MM-DD HH:MI:SS FF1 YYYY');
ERROR 2353 ER_STR_TO_DATE_CONVERSION: More digits than expected for fractional seconds
Example 4
The following example shows the usage of few valid punctuations that can be used as separators in convert_
and convert_
:
('10/APR}18 11.59.59.0 PM','DD-MON-RR HH:MI:SS.FF PM')('10)APR{18 11.59.59.00 PM','DD-MON-RR HH@MI[SS]FF PM')('10&APR|18 11.59.59.000000000 PM','DD-MON-RR HH:MI:SS~FF PM')('10$APR`18 11<59>59=00 PM','DD-MON-RR HH:MI:SS~FF PM')('10.APR.18', 'DD-MON-RR')('10/APR/18', 'DD-MON-RR')('10%APR/18', 'DD#MON!RR')
Last modified: May 31, 2023