How to convert a dd/mm/yyyy string to datetime

Whenever you work on legacy data, you usually expect some sort of data in incorrect data types, but of course with the compatibility. Recently, I migrated one of my client’s data from legacy system to SQL Server 2012 and I came across an issue where the client had a date in varchar field and the data had been placed in dd/mm/yyyy format in that field. The data type had to be changed from varchar to datetime. Lets try to convert it from dd/mm/yyyy (varchar) to datetime.

Given below is the script.

DECLARE @Varchar_Date varchar(11) SET @Varchar_Date='31/12/2012' SELECT CONVERT(datetime,@Varchar_Date) --OUTPUT

Msg 242, Level 16, State 3, Line 3
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Ooopsss, you cannot convert it directly. I continued with my research. Finally resolved it. Given below is the solution.

Solution :
In this solution, we will use TRY_PARSE function introduced in SQL Server 2012. Given below is the script.

--This script is compatible with SQL Server 2012 and above. DECLARE @Varchar_Date as varchar(11) SET @Varchar_Date='31/12/2013' SELECT TRY_PARSE(@Varchar_Date AS DATETIME USING 'en-GB') As [Result] --OUTPUT

Result
———————–
2013-12-31 00:00:00.000

(1 row(s) affected)

Let me know if you came across this issue and resolved it in a better way.
Note : For the earlier versions of SQL Servers, you can convert it like this.