Well, like I said the truly difficult part is figuring out if you should take the first or first and second digits as the month. Your example of '70599' shows that the day is already left-padded, so that is no problem.
Basically, the code would be something like this:
[tt]select cast(left('70599', 1) + "-" +
substring('70599', 2, 2) + "-" +
right('70599', 2) as datetime)[/tt]
But this won't work right for Oct-Dec, so you should wrap it in a CASE or IF statement, as in
[tt]declare @DateChar as char(6)
declare @DateValue as datetime
set @DateChar = '70599' --or your column name
if left(@DateChar,1) = '1'
set @DateValue = cast(left(@DateChar, 2) + "-" +
substring(@DateChar, 3, 2) + "-" +
right(@DateChar, 2) as datetime)
else
set @DateValue = cast(left(@DateChar, 1) + "-" +
substring(@DateChar, 2, 2) + "-" +
right(@DateChar, 2) as datetime)
[/tt]
Robert Bradley