Reporting Service - Convert Character String to datetime

[izziy]

Our legacy DB has a bad column which stores a date value as char(8), it has a format like 'MMDDYYYY'.

I am creating a report to report this particular date and also use this date to specify a range.

I tried using this

SELECT Convert(datetime,(substring(c.dateofreceipt,5,4)+'-'+substring(c.dateofreceipt,1,2)+'-'+substring(c.dateofreceipt,3,2)), 102) as dateofreceipt

in query analyzer and it works fine. But in .Net report designer, it gives me an error message like 'Syntax error converting datetime from character string".

Also, if I try Select Convert(datetime. '20070120', 102), it would work for both Analyzer and Designer. For some reason, it does not like the use of substring in the CONVERT function call.

Anyone knows how to take care of this problem?

I have tried to use cast, and it gave me the same problem.

Thanks in advance.

[kenobewan]

With db I find the datetime.parseexact method the best:
DateTime.ParseExact Method