Convert varchar into datetime in SQL Server

I found this helpful for my conversion, without string manipulation. https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

CONVERT(VARCHAR(23), @lastUploadEndDate, 121)

yyyy-mm-dd hh:mi:ss.mmm(24h) was the format I needed.

Likely you have bad data that cannot convert. Dates should never be stored in varchar becasue it will allow dates such as ASAP or 02/30/2009. Use the isdate() function on your data to find the records which can't convert.

OK I tested with known good data and still got the message. You need to convert to a different format becasue it does not know if 12302009 is mmddyyyy or ddmmyyyy. The format of yyyymmdd is not ambiguous and SQL Server will convert it correctly

I got this to work:

cast( right(@date,4) + left(@date,4) as datetime)

You will still get an error message though if you have any that are in a non-standard format like '112009' or some text value or a true out of range date.

SQL Server can implicitly cast strings in the form of 'YYYYMMDD' to a datetime - all other strings must be explicitly cast. here are two quick code blocks which will do the conversion from the form you are talking about:

version 1 uses unit variables:

BEGIN 
DECLARE @input VARCHAR(8), @mon CHAR(2), 
@day char(2), @year char(4), @output DATETIME

SET @input = '10022009'   --today's date


SELECT @mon = LEFT(@input, 2), @day = SUBSTRING(@input, 3,2), @year = RIGHT(@input,4)

SELECT @output = @year+@mon+@day 
SELECT @output 
END

version 2 does not use unit variables:

BEGIN 
DECLARE @input CHAR(8), @output DATETIME
SET @input = '10022009' --today's date 

SELECT @output = RIGHT(@input,4) + SUBSTRING(@input, 3,2) + LEFT(@input, 2)

SELECT @output
END

Both cases rely on sql server's ability to do that implicit conversion.

OP wants mmddyy and a plain convert will not work for that:

select convert(datetime,'12312009')

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

so try this:

DECLARE @Date char(8)
set @Date='12312009'
SELECT CONVERT(datetime,RIGHT(@Date,4)+LEFT(@Date,2)+SUBSTRING(@Date,3,2))

OUTPUT:

-----------------------
2009-12-31 00:00:00.000

(1 row(s) affected)