MaxCompute:Data type conversion: Convert between STRING,TIMESTAMP,and DATETIME

Conversion quick reference

The following table summarizes all supported conversion directions, the available functions, and whether a format string is required.

Data type formats:

• TIMESTAMP: yyyy-mm-dd hh:mi:ss.ff3

• DATETIME: yyyy-mm-dd hh:mi:ss

STRING to TIMESTAMP

CAST is the only function that converts a STRING value to TIMESTAMP. The input string must be in yyyy-mm-dd hh:mi:ss format (accurate to the second). Strings that do not meet this requirement return NULL.

-- Returns 2009-07-01 16:09:00.000
SELECT CAST('2009-07-01 16:09:00' AS TIMESTAMP);

-- Returns NULL: date-only strings are not accepted
SELECT CAST('2009-07-01' AS TIMESTAMP);

STRING to DATETIME

Use CAST or TO_DATE to convert a STRING value to DATETIME. Both functions require the input string to be in yyyy-mm-dd hh:mi:ss format.

Using CAST:

-- Returns 2009-07-01 16:09:00
SELECT CAST('2009-07-01 16:09:00' AS DATETIME);

-- Returns NULL: date-only strings are not accepted
SELECT CAST('2009-07-01' AS DATETIME);

Using TO_DATE — set the format parameter to yyyy-mm-dd hh:mi:ss:

-- Returns 2009-07-01 16:09:00
SELECT TO_DATE('2009-07-01 16:09:00', 'yyyy-mm-dd hh:mi:ss');

-- Returns NULL: format parameter does not match the abbreviated input
SELECT TO_DATE('2009-07-01', 'yyyy-mm-dd hh:mi:ss');

TIMESTAMP to STRING

Use CAST or TO_CHAR to convert a TIMESTAMP value to STRING.

To construct data of the TIMESTAMP type from a string, you must use the CAST function before applying the conversion.

Using CAST — requires two nested CAST calls: the inner CAST constructs the TIMESTAMP value, and the outer CAST converts it to STRING:

-- Returns 2009-07-01 16:09:00
SELECT CAST(CAST('2009-07-01 16:09:00' AS TIMESTAMP) AS STRING);

Using TO_CHAR — pass the TIMESTAMP value as the first argument and an optional format string as the second:

-- Returns 2009-07-01 16:09:00
SELECT TO_CHAR(CAST('2009-07-01 16:09:00' AS TIMESTAMP), 'yyyy-mm-dd hh:mi:ss');

TIMESTAMP to DATETIME

Use CAST or TO_DATE to convert a TIMESTAMP value to DATETIME. As with TIMESTAMP to STRING conversions, construct the TIMESTAMP value first using an inner CAST.

Using CAST:

-- Returns 2009-07-01 16:09:00
SELECT CAST(CAST('2009-07-01 16:09:00' AS TIMESTAMP) AS DATETIME);

Using TO_DATE — set the format parameter to yyyy-mm-dd hh:mi:ss:

-- Returns 2009-07-01 16:09:00
SELECT TO_DATE(CAST('2009-07-01 16:09:00' AS TIMESTAMP), 'yyyy-mm-dd hh:mi:ss');

DATETIME to TIMESTAMP

CAST is the only function that converts a DATETIME value to TIMESTAMP. Use GETDATE() to get the current DATETIME value.

-- Returns the current timestamp, for example: 2021-10-14 10:21:47.939
SELECT CAST(GETDATE() AS TIMESTAMP);

DATETIME to STRING

Use CAST or TO_CHAR to convert a DATETIME value to STRING.

Using CAST:

-- Returns the current date and time as a string, for example: 2021-10-14 10:21:47
SELECT CAST(GETDATE() AS STRING);

Using TO_CHAR — pass an optional format string to control the output:

-- Full datetime: 2021-10-14 10:21:47
SELECT TO_CHAR(GETDATE(), 'yyyy-mm-dd hh:mi:ss');

-- Date only: 2021-10-14
SELECT TO_CHAR(GETDATE(), 'yyyy-mm-dd');

-- Year only: 2021
SELECT TO_CHAR(GETDATE(), 'yyyy');