SQL Serverous

SQL Serverous

Alan Wood  //  Professionally: SQL Server consultant
Personally: Geek, musician, sports fan (avid follower of Motherwell in the SPL and the 49ers and Dolphins in the NFL. Also follow the SF Giants in MLB).

Jan 7 / 4:19am

Formatting Dates in SSRS

In Reporting Services, formatting dates can become a headache for the developer, often due to differing regional settings on servers, development boxes etc.  Therefore I've found it much more useful to force the report to use a particular format, rather than allow the server settings to decide for me.  Previously I would have used FormatDateTime in the expression, as shown here:

=FormatDateTime(Fields!DateStamp.Value, DateFormat.ShortDate)

Depending on the regional settings, this could return either 12/3/2008 or 3/12/2008.  When designing the report, I want to know exactly what the date will look like, no matter where it is deployed.  So I instead use the Format function to allow me to choose the format I require.  Some examples:

=Format(Fields!DateStamp.Value, "dd-MMM-yy")
returns 03-Dec-08

=Format(Fields!DateStamp.Value, "dd-MMM-yyyy")
returns 03-Dec-2008

=Format(Fields!DateStamp.Value, "dd-MMMM-yy")
returns 03-December-08

=Format(Fields!DateStamp.Value, "dd-MMM-yyyy H:mm")
03-Dec-2008 16:44

This method removes a lot of the issues regarding date formatting, and means there are no shocks or questions when it comes to deployment time!

Edit: Setting the Language property of the report to your locale will also ensure that dates in your report parameters will have the correct format.  This is something that is often overlooked.

0 comments

Leave a comment...

 
Got an account with one of these? Login here, or just enter your comment below.
Posterous-login    Connect    twitter