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);
- Example 1: Use the CAST function to convert the string
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
- 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
.
- If you use the CAST function, the date value of the STRING type must be specified
in the
- 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');
- Example 1: Use the CAST function to convert the string
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
- 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');
- Example 1: Use the CAST function to convert the TIMESTAMP value
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 theyyyy-mm-dd hh:mi:ss
format. - Conversion methods
- 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');
- Example 1: Use the CAST function to convert the TIMESTAMP value
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 theyyyy-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
- 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');
- 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: