This topic describes how to convert data types among STRING, TIMESTAMP, or DATETIME. This topic provides multiple date conversion methods that you can use to improve your business efficiency.

STRING to TIMESTAMP

  • Scenarios

    Convert a date value of the STRING type to the TIMESTAMP type. The date value of the TIMESTAMP type is in the yyyy-mm-dd hh:mi:ss.ff3 format.

  • Conversion methods

    Use the CAST function.

  • Limits

    Date values of the STRING type must be at least accurate to the second and must be specified in the yyyy-mm-dd hh:mi:ss format.

  • Examples
    • Example 1: Use the CAST function to convert the string 2009-07-01 16:09:00 to the TIMESTAMP type. Sample statement:
      -- The return value is 2009-07-01 16:09:00.000. 
      select cast('2009-07-01 16:09:00' as timestamp);
    • Example 2: Incorrect usage of the CAST function
      -- The return value is NULL because the input data value is invalid. The date value must be in the yyyy-mm-dd hh:mi:ss format. 
      select cast('2009-07-01' as timestamp);

STRING to DATETIME

  • Scenarios

    Convert a date value of the STRING type to the DATETIME type. The date value of the DATETIME type is in the yyyy-mm-dd hh:mi:ss format.

  • Conversion methods
    • Method 1: Use the CAST function.
    • Method 2: Use the TO_DATE function.
  • Limits
    • If you use the CAST function, the date value of the STRING type must be specified in the yyyy-mm-dd hh:mi:ss format.
    • If you use the TO_DATE function, you must set the value of the format parameter to yyyy-mm-dd hh:mi:ss.
  • Examples
    • Example 1: Use the CAST function to convert the string 2009-07-01 16:09:00 to the DATETIME type. Sample statement:
      -- The return value is 2009-07-01 16:09:00. 
      select cast('2009-07-01 16:09:00' as datetime);
    • Example 2: Use the TO_DATE function and specify the format parameter to convert the string 2009-07-01 16:09:00 to the DATETIME type. Sample statement:
      -- The return value is 2009-07-01 16:09:00. 
      select to_date('2009-07-01 16:09:00','yyyy-mm-dd hh:mi:ss');
    • Example 3: Incorrect usage of the CAST function
      -- The return value is NULL because the input data value is invalid. The date value must be in the yyyy-mm-dd hh:mi:ss format. 
      select cast('2009-07-01' as datetime);
    • Example 4: Incorrect usage of the TO_DATE function
      -- The return value is NULL because the input data value is invalid. The date value must be in the yyyy-mm-dd hh:mi:ss format. 
      select to_date('2009-07-01','yyyy-mm-dd hh:mi:ss');

TIMESTAMP to STRING

  • Scenarios

    Convert a date value of the TIMESTAMP type to the STRING type. The value of the TIMESTAMP type is in the yyyy-mm-dd hh:mi:ss.ff3 format.

  • Conversion methods
    • Method 1: Use the CAST function.
    • Method 2: Use the TO_CHAR function. The format of the value after the conversion is specified by the format parameter.
  • Examples
    • Example 1: Use the CAST function to convert the TIMESTAMP value 2009-07-01 16:09:00 to the STRING type. To construct data of the TIMESTAMP type, you must use the CAST function twice. Sample statement:
      -- The return value is 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 value 2009-07-01 16:09:00 to the STRING type. To construct data of the TIMESTAMP type, you must use the CAST function once. Sample statement:
      -- The return value is 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

  • Scenarios

    Convert a date value of the TIMESTAMP type to the DATETIME type. Before the conversion, the date value of the TIMESTAMP type is in the yyyy-mm-dd hh:mi:ss.ff3 format. After the conversion, the date value of the DATETIME type is in the yyyy-mm-dd hh:mi:ss format.

  • Conversion methods
    • Method 1: Use the CAST function.
    • Method 2: Use the TO_DATE function.
  • Limits

    If you use the TO_DATE function, you must set the value of the format parameter to yyyy-mm-dd hh:mi:ss.

  • Examples
    • Example 1: Use the CAST function to convert the TIMESTAMP value 2009-07-01 16:09:00 to the DATETIME type. To construct data of the TIMESTAMP type, you must use the CAST function twice. Sample statement:
      -- The return value is 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 value 2009-07-01 16:09:00 to the DATETIME type. To construct data of the TIMESTAMP type, you must use the CAST function once. Sample statement:
      -- The return value is 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

  • Scenarios

    Convert a date value of the DATETIME type to the TIMESTAMP type. Before the conversion, the date value of the DATETIME type is in the yyyy-mm-dd hh:mi:ss format. After the conversion, the date value of the TIMESTAMP type is in the yyyy-mm-dd hh:mi:ss.ff3 format.

  • Conversion methods

    Use the CAST function.

  • Examples
    Use the CAST function to convert a DATETIME value to the TIMESTAMP type. To construct data of the DATETIME type, you must use the GETDATE function once. Sample statement:
    -- The return value is 2021-10-14 10:21:47.939. 
    select cast(getdate() as timestamp);

DATETIME to STRING

  • Scenarios

    Convert a date value of the DATETIME type to the STRING type. The date value of the DATETIME type is in the yyyy-mm-dd hh:mi:ss format.

  • Conversion methods
    • Method 1: Use the CAST function.
    • Method 2: Use the TO_CHAR function. The format of the value after the conversion is specified by the format parameter.
  • Examples
    • Example 1: Use the CAST function to convert a DATETIME value to the STRING type. To construct data of the DATETIME type, you must use the GETDATE function once. Sample statement:
      -- The return value is 2021-10-14 10:21:47. 
      select cast(getdate() as string);
    • Example 2: Use the TO_CHAR function to convert a DATETIME value to the STRING type in the specified format. To construct data of the DATETIME type, you must use the GETDATE function once. Sample statements:
      -- The return value is 2021-10-14 10:21:47. 
      select to_char (getdate(),'yyyy-mm-dd hh:mi:ss');
      -- The return value is 2021-10-14. 
      select to_char (getdate(),'yyyy-mm-dd');
      -- The return value is 2021. 
      select to_char (getdate(),'yyyy');