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:

As always, please leave your comments if you like this article 🙂

Advertisements

About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

Posted on February 10, 2011, in SQL Server, SSRS. Bookmark the permalink. 7 Comments.

  1. Good post! Saved my day!

  2. One of the most commonly used formats doesn't seem to work anymore on 2008 R2.The ISO standard of –> "yyyy-MM-dd HH:mm:ss"Curious if I'm the only one who has experienced or noticed this….

  3. Nevermind. ISO standard works just fine.

  4. how to format the date and time and creating reports in sql

  5. Realy Goood. Thnx So much!!

  1. Pingback: SQL Server Reporting Services Tips and Tricks to Improve the End-User Experience | Datta's Ramblings on Business Intelligence 'N' Life

  2. Pingback: SSRS – SQL Server Reporting Services Report Manager Tips and Tricks | Datta's Ramblings on Business Intelligence 'N' Life

What are your thoughts?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s