select records.DATE_RES_MADE from ( select stuff(stuff( case when len(DATE_RES_MADE) < 6 then '0' + convert(varchar(6) ,resdate) else convert(varchar(6) ,resdate) end, 3, 0, '/'), 6, 0, '/') as DATE_RES_MADE from RES ) records where records.DATE_RES_MADE > dateadd(dd, datediff(dd, 0, getdate()) -1, 0);Otherwise, you will have to copy the select part into the where clause in place of your DATE_RES_MADE.
Msg 242, Level 16, State 3, Line 7 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.Because of it, Matt's way appears safer because it is resilient to the problem with 5 digits instead of 6. Additionally, if to modify it slightly to append '20' or '19' depending on the year to the left then his convert results in the YYYYMMDD, which is independent of any local settings and does not need a format parameter: select convert(datetime, '20' + convert(varchar, floor([resdate] / 100) + ((resdate % 100) * 10000))) from @dates; Unfortunately, the statement above is not resilient to the problem of the date where year part is less than 10, such as **103105** This means that the answer still requires further tweaking :)
select stuff(stuff( case when len(resdate) < 6 then '0' + convert(varchar(6) ,resdate) else convert(varchar(6) ,resdate) end, 3, 0, '/'), 6, 0, '/') from @dates;Kev was exactly right when he said:
Yep there are sooooooooo many ways
No one has followed this question yet.