Convert between STRING, TIMESTAMP, and DATETIME

Scenario

Convert a STRING value to a TIMESTAMP value in the yyyy-mm-dd hh:mi:ss.ff3 format.

Method

Use the CAST function.

Limitation

The input STRING value must at least be in the yyyy-mm-dd hh:mi:ss format.

Example

• Example 1: Use the CAST function to convert the STRING value 2009-07-01 16:09:00 to TIMESTAMP.

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

• Example 2: An incorrect use of the CAST function.

  -- Returns NULL because the input format is invalid. The format must be at least 'yyyy-mm-dd hh:mi:ss'.
  SELECT CAST('2009-07-01' AS TIMESTAMP);

Scenario

Convert a STRING value to a DATETIME value in the yyyy-mm-dd hh:mi:ss format.

Method

• Method 1: Use the CAST function.

• Method 2: Use the TO_DATE function.

Limitation

• The CAST function requires the input STRING value to be in the yyyy-mm-dd hh:mi:ss format.

• For the TO_DATE function, set the format parameter to yyyy-mm-dd hh:mi:ss.

Example

• Example 1: Use the CAST function to convert the STRING value 2009-07-01 16:09:00 to DATETIME.

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

• Example 2: Use the TO_DATE function with the format parameter to convert the STRING data 2009-07-01 16:09:00 to the DATETIME type. The command is as follows.

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

• Example 3: An incorrect use of the CAST function.

  -- Error: ODPS-0121095:Invalid argument - in function cast, string datetime's format must be yyyy-mm-dd hh:mi:ss,  input string is:2009-07-01
  -- The input data format is invalid. The format must be yyyy-mm-dd hh:mi:ss.
  SELECT CAST('2009-07-01' AS DATETIME);

• Example 4: An incorrect use of the TO_DATE function.

  -- Error: ODPS-0121095:Invalid argument - in function to_date, format string does not match datetime string, datetime string value is '2009-07-01' and format string is 'yyyy-mm-dd hh:mi:ss'
  -- The input string '2009-07-01' does not match the specified format 'yyyy-mm-dd hh:mi:ss'.
  SELECT TO_DATE('2009-07-01','yyyy-mm-dd hh:mi:ss');

Scenario

Convert a TIMESTAMP value (in the yyyy-mm-dd hh:mi:ss.ff3 format) to a STRING value.

Method

• Method 1: Use the CAST function.

• Method 2: Use the TO_CHAR function with the format parameter to control the output format.

Example

• Example 1: Use the CAST function to convert the TIMESTAMP value 2009-07-01 16:09:00 to the STRING type. The command is as follows.

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

• Example 2: Use the TO_CHAR function to convert the TIMESTAMP data 2009-07-01 16:09:00 to the STRING type. The following is a sample command.

  -- 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');

Scenario

Convert a TIMESTAMP value (in the yyyy-mm-dd hh:mi:ss.ff3 format) to a DATETIME value (in the yyyy-mm-dd hh:mi:ss format).

Method

• Method 1: Use the CAST function.

• Method 2: Use the TO_DATE function.

Limitation

For the TO_DATE function, set the format parameter to yyyy-mm-dd hh:mi:ss.

Example

• Example 1: Use the CAST function to convert the TIMESTAMP data 2009-07-01 16:09:00 to the DATETIME type. The following is a sample command.

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

• Example 2: Use the TO_DATE function and specify the format parameter to convert the TIMESTAMP data 2009-07-01 16:09:00 to the DATETIME type. The following is a sample command.

  -- 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');

Scenario

Convert a DATETIME value (in the yyyy-mm-dd hh:mi:ss format) to a TIMESTAMP value (in the yyyy-mm-dd hh:mi:ss.ff3 format).

Method

Use the CAST function.

Example

-- Returns the current time as a TIMESTAMP, for example: 2021-10-14 10:21:47.000.
SELECT CAST(GETDATE() AS TIMESTAMP);

Scenario

Convert a DATETIME value (in the yyyy-mm-dd hh:mi:ss format) to a STRING value.

Method

• Method 1: Use the CAST function.

• Method 2: Use the TO_CHAR function with the format parameter to control the output format.

Example

• Example 1: Use the CAST function to convert a DATETIME value to STRING.

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

• Example 2: Use the TO_CHAR function to convert a DATETIME value to a STRING in a specified format.

  -- Returns the current date and time, for example: 2021-10-14 10:21:47.
  SELECT TO_CHAR(GETDATE(),'yyyy-mm-dd hh:mi:ss');
  -- Returns the current date, for example: 2021-10-14.
  SELECT TO_CHAR(GETDATE(),'yyyy-mm-dd');
  -- Returns the current year, for example: 2021.
  SELECT TO_CHAR(GETDATE(),'yyyy');