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

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

Convert () is used to convert a given value of one data type to another data type.

Convert into DateTime type by using Convert () function:

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

Select CONVERT (Datetime,'02/12/2019')

Output:

2019-02-12 00:00:00.000

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

Example:

Select CONVERT (Datetime,'12S2019')

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 CONVERT (Datetime,'02/31/2019')

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 Convert () function:

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

Select CONVERT (Date,'02/18/2019')

Output:

2019-02-18

Convert into time type by using Convert () function:

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

Select CONVERT (Time,'02/18/2019 09:02')

Output:

09:02:00.0000000

Select CONVERT (Time,'09PM')

Output:

21:00:00.0000000