SQL Server cast string to datetime

I have a string 010910 in ddMMyy format thatI have to ca开发者_StackOverflow社区st this string a SQL Server datetime datatype, like 2010-09-01 00:00:00.000.

How can this be done?

How about something like

DECLARE @String VARCHAR(6)
SELECT  @String = '010910'

SELECT  CONVERT(DATETIME,LEFT(@String,2) + '/' + SUBSTRING(@String, 3, 2) + '/' + RIGHT(@String,2),3)

Have a look at SQL Server Date Formats and CAST and CONVERT (Transact-SQL)

SELECT CAST(right(s, 2) + left(s,4) as datetime)
FROM (SELECT '010910' s) a

Pull the bits out using SUBSTRING and put them back together by concatenating.