How to convert string field to datetime in Reporting Services?

I am pulling data from a database whose field is not in the bes format.

Specifically, it is in this text format although it should be a datetime...


20070305 for 2007, 3rd month, 5 day.

How do I convert it to a date or datetime field for display in Reporting Services?
Who is Participating?
chrismcConnect With a Mentor Commented:
Ideally you'd do this from SQL. Convert(DateTime, myDate) will probably do it. Then it would be a true datetime field and formatting would then be automatic to your location, i.e. US = m d y, almost everywhere else = dd mm yy.

You can use "Substring" in SQL or "Mid" in VB to extract parts of the string and reformat it the way you want.
The most universal format for storing dates is yyyy-mm-dd. If you can get it in this format then date functions and formatting will work properly.

YurichConnect With a Mentor Commented:
copletely agree with Chris that you should do it on the SQL side whenever it's possible, but in the case you have to do it in your report (you got no access to your SP, or no rights, or whatever), here is the formula:

=Format( CDate( Left( Parameters!param.Value, 4 ) & "/" &
 Mid( Parameters!param.Value, 5, 2 ) & "/" &
 Right( Parameters!param.Value, 2 )), "yyyy/MM/dd" )

Parameter!param.Value is your "20050305" string, you can change it to your field name. By default CDate will produce date time in the format yyyy-MM-dd HH:mm:ss, so I used Format as well, just to have date part.

Good luck,
M3hcSSAuthor Commented:
Both these solutions work very well.

However, the second solution posted by Yurich requires fixed-width fields; if the date is missing, say, leading zeroes then it won't work.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.