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
