Formatting Date and Time in SQL Server Reporting Services (SSRS) Reports

Formatting Date and Time in SQL Server Reporting Services (SSRS) is a very common need. And in some cases customers look for a very specific format, so it is important to understand the basic components of date and time formats.

SSRS offers many in-built Date and Time formats. Below are some of the in-built Date formats available in SSRS:

Below are some of the in-built Time formats available in SSRS:

However, sometimes customers need a different format which is not available using SSRS UI (In-built). For example let us say that customers want the Date and Time to be formatted as shown below.

09-Feb-2011 10:47:40 PM (GMT+05:30)

To address this kind of requirements, we need to understand the various formatting codes/strings that are supported in SSRS. Below is a list of various format codes available in SSRS.

Format Code	Description	Example
d	Single Digit Date Format	9
dd	Double Digit Date Format	09
ddd or dddd	Full Day Name	Wednesday
M	Single Digit Month Format	2
MM	Double Digit Month Format	02
MMM	Three Character Month Name	Feb
MMMM	Full Month Name	February
yy	Double Digit Year Format	11
yyyy	Four Digit Year Format	2011
H	Single Digit "24 Hour" Hour Format	6
HH	Double Digit "24 Hour" Hour Format	06
m	Single Digit Minute Format	9
mm	Double Digit Minute Format	09
s	Single Digit Second Format	4
ss	Double Digit Second Format	04
tt	ante meridian (AM)/post meridian (PM) part of Time	PM
zzz	Time Zone Offset w.r.t. GMT	+05:30

With reference to the format codes given in the above table, please note the following:

• Format codes are case-sensitive in most cases. Like m represents Minutes and M represents Month.
• Format codes can be combined using some meaningful characters like "-", "," etc. to customize the Date and Time display.

Below is a sample report which displays various combinations of Date and Time values.

The .rdl file for the demo report in the above screenshot can be downloaded from my SkyDrive here.

Note: This demo is based on SQL Server 2008 R2 Reporting Services.

Following are some of the most popular articles related to formatting in SSRS:

• Formatting Minutes as Hours and Minutes in SQL Server Reporting Services (SSRS)
• Adding Alternate Row Colors in SQL Server Reporting Services (SSRS)

As always, please leave your comments if you like this article :-)

Readers Feedback:

Did you like this article? If Yes, then please share it: | More Options

Did you like my blog? If Yes, then please help spread the word by sharing this blog with your colleagues, friends, & anyone else in the MSBI Space: Like us on Facebook , Follow us on Twitter .