TO_TIMESTAMP
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_from_format
may contain characters that are not format specifiers. These characters must appear in the same position inconvert_from_value
else, the function will returnNULL
.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_from_value
andconvert_from_format
values, all punctuations can be used as separators ( ! " # $ % & ’ ( ) * + , - . / : ; < = > ? @ [ \ ] ^ _ ` { ¦ } ~ ). The separator inconvert_from_value
may not match the separator inconvert_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_TIMESTAMP
, allowing the function to successfully return a TIMESTAMP(6)
value. If this string were to be excluded from both arguments, the output would remain the same as shown below.
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_TIMESTAMP
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_from_value
and convert_from_format
:
('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')