ddmmyyyy to sql datetime in SQL

Question

I need to convert a nvarchar value to datetime in T-SQL. The value is in ddmmyyyy format, e.g. 23072009

I need to convert to datetime in T-SQL.

I tried

select convert(datetime, '23072009', 103)

But it is throwing error.

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

Any idea

Thanks

user_4290650 · Answer

Change VARCHAR (dd-mm-yyyy) to date (yyyy-mm-dd) in SQL Server.

DECLARE @VarDate VARCHAR(10)
SET @VarDate  = '22-02-1994'

SELECT CONVERT(DATETIME, CONVERT(VARCHAR, CONVERT(DATE, @VarDate, 103), 102))