Formatting data for reports seems like 90% of the work involved. Only show whole numbers, add a percent sign to this column, bold that, blah blah blah. Of particular fun is dealing with dates. Personally, I prefer to see dates in yyyy/MM/dd or dd/MM/yyyy format. It just makes more sense than the American format of MM/dd/YYYY.
Enough griping, in SSRS (Microsoft SQL Server Reporting Services) there are several ways you can format your dates. Take your pick:
Formatting in the Query
TSQL provides several choices for getting your date formatted:
CONVERT
TSQL has a built in function that will convert your dates to predefined formats. Unfortunately there aren’t enumerations to make it easy, you have to remember the magic numbers for the format you want, and I’m going to assume you want 4 digit years. If you don’t you can look it up in MSDN easily enough:
· CONVERT(VARCHAR(30), GETDATE(), 101) will produce “09/29/2009”
· CONVERT(VARCHAR(30), GETDATE(), 103) will produce “29/09/2009”
· CONVERT(VARCHAR(30), GETDATE(), 112) will produce “20090929”
There are several other formats, but these are the main ones you’ll want to use most of the time. However, perhaps you want different dividers or something. That brings up the other TSQL option.
DAY, MONTH, YEAR
The other TSQL option is to just concatenate the string yourself. To do that just call the DAY, MONTH and YEAR functions with the date you are working with:
SELECT YEAR(GETDATE()) + ‘-‘ + MONTH(GETDATE()) + ‘-‘ + DAY(GETDATE()) will produce “2009-09-29”
This approach provides the greatest flexibility but requires a bit more work than the built in CONVERT function.
Suppose, for whatever reason, you can’t do the work in the query. You are not limited to formatting in the report’s query. You can do formatting work in the report too.
Formatting in the Report
Once you have your report started you can right click in the fields and select properties (TextBox properties). In the General tab go to the value field and click the formula button. In there you can enter a Visual Basic expression to format your date. You can go pretty wild here and even call out to custom assemblies. However, for our purposes here we just need to format a date. We can use the FormatDateTime fuction. FormatDateTime has 5 formats available, but be aware that they use the regional settings on the server that is running the report. The format choices are:
· vbGeneralDate
· vbLongDate
· vbShortDate
· vbLongTime
· vbShortTime
Overall I think this approach to formatting a date is pretty useless b/c you are giving up control to a system setting that might not be set the way you expect. Imagine that you deploy the report, get everything setup the way you want and switch to something else. Three months later along comes a new sysadmin who decides he likes some other regional setting better (the reason isn’t relevant, the BOFH doesn’t need a reason). Now you get to spend a day trying to figure out why your report looks different because you won’t remember that the regional setting determines the format for your date. The solution? Don’t do that.
Build the date format using either string.format or plain old string concatenation:
· String.format(“{0}-{1}-{2}”, Year(Fields!DateCreated.Value), Month(Fields!DateCreated.Value), Day(Fields!DateCreated.Value))
· Year(Fields!DateCreated.Value) & “-“ & Month(Fields!DateCreated.Value) & “-“ & Day(Fields!DateCreated.Value)
· Or Now.ToString(“yyyy-MM-dd”)
Both achieve the same result so it is just a matter of style preference. I’d probably use the DateTime.ToString() approach because it provides the greatest degree of control.
Where to do the formatting
Sense you have a choice; where should you format data? I would suggest doing the formatting inside the report. That way you can reuse the query. Formatting tends to remove information and tends to be for specific purposes. If you can put that logic in the report itself, instead of the query, then you’ll have less work to do when asked to make another report using the exact same data.
Conclusion
Formatting a date really is difficult, but there are a number of choices available. Try to format in your report instead of the query and use DateTime.ToString to format the date to get exactly what you are after.