SQL - String to Date/Time Conversion by using Cast ( ) function

In this blog, we will learn how to convert the string into Date/Time type by using Cast () SQL function.

Cast () is used to convert a given value of one data type to another data type and Convert () function works almost same as Cast () function except syntax different.

Convert into DateTime type by using Cast () function:

The below SQL script is used to convert date string into DateTime type

Select CAST ('12/31/2019' AS DateTime)

Output:

2019-12-31 00:00:00.000

If input value is not valid date sting, it throw below exception

Example:

Select CAST ('12S2019' AS DateTime)

Exception:

Conversion failed when converting date and/or time from character string.

If input string is date string but it is invalid date, it throw below exception

Example: 31th day is invalid for February month

Select CAST ('02/31/2019' AS DateTime)

Exception:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Convert into Date type by using Cast () function:

The below SQL script is used to convert input string into Date type (only date information)

Select CAST ('02/18/2019' AS date)

Output:

2019-02-18

Convert into time type by using Cast () function:

The below SQL script is used to convert input string into time type

Select CAST ('02/18/2019 09:02' AS time)

Output:

09:02:00.0000000

Select CAST ('09pm' AS time)

Output:

21:00:00.0000000