SQL Converting character to datetime

Does anybody now how to 开发者_开发技巧convert this character to date or datetime: '10022011'

---

Quick & dirty:

select convert(datetime, stuff(stuff('10022011', 5,0,'-'),3,0,'-'))

However, you might want to consider converting the string into ISO standard date format:

declare @d char(8)
select @d = '10022011'

select convert(datetime, substring(@d,5,4) + '-' + substring(@d,3,2) + '-' + substring(@d, 1, 2))

in order to avoid ambiguity. 10-02-2011 has different meanings depending on which side of the pond you are.

---

SELECT convert(datetime, STUFF(STUFF('10022011',3,0,'-'),6,0,'-'), 103) 

the number at the end is the Sql format you want the date to output