Skip to main content

SingleStoreDB v8.1

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 in convert_from_value else, the function will return NULL.

  • Format specifiers YYYY and YY returns the results in YYYY 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 and convert_from_format values, all punctuations can be used as separators ( ! " # $ % & ’ ( ) * + , - . / : ; < = > ? @ [ \ ] ^ _ ` { ¦ } ~ ). The separator in convert_from_value may not match the separator in 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_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 TO_TIMESTAMP

Result

('10-APR-18 11:59:59 PM','DD-MON-RR HH:MI:SS.FF PM')

2018-04-10 23:59:59

('10-APR-18 11:59:59 PM','DD-MON-RR HH:MI:SS.FF1 PM')

2018-04-10 23:59:59

('10-APR-18 11:59:59 PM','DD-MON-RR HH:MI:SS.FF9 PM')

2018-04-10 23:59:59

('10-APR-18 11:59:59 PM','DD-MON-RR HH:MI:SS FF PM')

2018-04-10 23:59:59.000000

('10-APR-18 11:59:59 PM','DD-MON-RR HH:MI:SS FF1 PM')

2018-04-10 23:59:59.000000

('10-APR-18 11:59:59. PM','DD-MON-RR HH:MI:SS.FF PM')

2018-04-10 23:59:59.000000

('10-APR-18 11:59:59.000000000 PM','DD-MON-RR HH:MI:SS.FF PM')

2018-04-10 23:59:59.000000

('11.59.59.1234567892018','HH.MI.SS.FFYYYY')

2018-04-01 11:59:59.123456

('04-10 11:59:59 123 2018','MM-DD HH:MI:SS FF8 YYYY')

2018-04-10 11:59:59.123000

('11.59.59.1234567890','HH.MI.SS.FF')

2019-04-01 11:59:59.123456

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')
In this section: