Saturday, March 17, 2012

SSRS Date time format conversions

Hi, Today's problem is someone has a date field coming out of his source and has no control to convert it to any other format, it's coming as  MM-DD-YYYY. and want it to be displayed in YYYY-MM-DD.

So I beleive that's how it could be done...

The easy way.....

=Format( Fields!BadDate.Value ,"yyyy-MM-dd")  --If the data type is DATE

=Format(CDATE( Fields!BadDate.Value ),"yyyy-MM-dd") --If the data type is String

The hard way.....

=Year(CDATE(Fields!BadDate.Value))&"-"&Month(CDATE(Fields!BadDate.Value))&"-"&Day(CDATE(Fields!BadDate.Value))



Another scenario when you need to display only the Month Name and the year, which happens and needed a lot...

So if you date is 5/3/2012, and you need to display May 2012, you would use:


The easy way.....

=Format( Fields!BadDate.Value ,"MMM yyyy")
Or the hard way.....
=MonthName(Month(Fields!BadDate.Value )) &" " & Year(Fields!BadDate.Value )

Various SSRS conversions:

ExpressionResults
FormatDateTime(Fields!BadDate.Value,1)Thursday, May 3, 2012
FormatDateTime(Fields!BadDate.Value,2)5/3/2012
FormatDateTime(Fields!BadDate.Value,3)12:00:00 AM
FormatDateTime(Fields!BadDate.Value,4)00:00


Hope that helps someone...