It's very interesting how this starts as a concatenation theme, but now it has become an implicit conversion issue. By the way, the only implicit conversion in wich I trust is string to datetime. It will always works if the string provided is in 'yyyymmdd' format.

declare @s-2 nvarchar(8)

, @d1 datetime

, @d2 datetime

, @d3 datetime

set @s-2 = '20120821'

set dateformat dmy;

set @d1 = @s-2

select @d1 as date1

set dateformat mdy;

set @d2 = @s-2

select @d2 as date2

set dateformat ymd;

set @d3 = @s-2

select @d3 as date3

🙂