Home
> Reporting Services > Working with Dates in Reporting Services
Working with Dates in Reporting Services
As with any other technology ( eg. SQL , C# ), you always find people running into problems with processing of dates. I was asked today by a colleague "How to I format dates when using the Date Picker and sending it through to a stored procedure" ? Let’s have a look ….
1) The FormatDateTime command
This is pretty easy to use, but maybe a bit limiting. You can specify 1 of 4 formats using the command arguments. Let’s say we have selected a date such as 10th April 2007 , our results will be as follows :
| Command | Result |
| FormatDateTime(Parameters!Date.Value,1) | Tuesday, April 10, 2007 |
| FormatDateTime(Parameters!Date.Value,2) | 4/10/2007 |
| FormatDateTime(Parameters!Date.Value,3) | 12:00:00 AM |
| FormatDateTime(Parameters!Date.Value,4) | 00:00 |
…but the better way to do it would be to use …
2) The Format command and specify the exact format you require. For example…
| Command | Result |
| Format(Parameters!Date.Value,"dd-MM-yyyy") | 10-04-2007 |
| Format(Parameters!Date.Value,"dd/MM/yyyy") | 10/04/2007 |
| Format(Parameters!Date.Value,"MMM-dd-yyyy") | Apr-10-2007 |
| Format(Parameters!Date.Value,"MMM-dd-yy") | Apr-10-07 |
So 3 M’s give you "Apr" ….anyway this is quite useful if you’re looking for Day/Month/Year , since the system will default to MM/DD/YYYY.
Using this you should be able to display the date format you want , or send through a particular format to a Stored Proc.
EDITED 22/08/2007 : If the Format Command doesn’t work , try converting the value to a date , eg.
| Format(Cdate(Parameters!Date.Value),"dd-MM-yyyy") |
Categories: Reporting Services
Thavash's Tech Blog 
There is another way to use the FormatDateTime command:
FormatDateTime(Parameters!Date.Value, 0) –> 6/15/2007 6:20:07 PM
LikeLike
can describe
I have report with datetime parameter and I need send this parameter in format dd/MM/yyyy to my dataset
How I can use Format(Parameters!Date.Value,"dd/MM/yyyy") in my case ?
LikeLike
You certainly helped me. Thanks 🙂
LikeLike
The Format command works great. Thanks for this.
LikeLike
Just what i need!
LikeLike
Thanks!
LikeLike
thank you, this is very useful. straight to the point.
LikeLike
Thanks, it\’s exactly what i need!!!
LikeLike
Solved my problem for me! Cheers buddy.
LikeLike
Thanks for the info, saved me time!
LikeLike
Thanks mate! Excellent solution. Saved my day!!! Thumbs Up
LikeLike
Thanks Buddy !!! Excellent stuff. Keep up the Good Work!!
LikeLike
Thanks Buddy
LikeLike
Thanks from me, too…
LikeLike
its great !
LikeLike
thanks…
LikeLike
I want to be able to export the date field to excel. When I use Format(FieldName,”dd-MMM-yyyy”) in reporting serivices, it looks like a date but imports as text. Any ideas what I could do?
Stuck!
LikeLike
Thanks man, it was very useful.
LikeLike
None of these work with SQL sever 2005 reporting services. Did anyone have any luck! Be honest.
I have a datetime report parameter where user selects starts date from calendar. What I like to do is have it show up todays date by default. How can i do this in SSRS 2005
LikeLike
In Report parameters tab – > Ur Date Parameter -> Default Values -> =Today
LikeLike
Thanks Naren !
LikeLike
in reporting services i hve expression as
=iIf(cdate(Fields!dtfrm.Value)=”01-01-1900″,””,cdate(Fields!dtfrm.Value)+ ” to “+cdate(Fields!dtto.Value) )
but its not working
if the date is 01/01/1900 i want the field to be displayed as blank else the va;ues
LikeLike
Did you ever get this working SVK? IF so do you mind sharing how?
LikeLike
I need in “dd-MM-yyyy hh:mm:ss” format …. Could any one suggest please….
LikeLike
LikeLike
Format(Now,“dd-MM-yyyy hh:mm:ss tt”)
LikeLike
Thank you for the post. It’s exactly the thing I was looking for. Thank you for the tip on Cdate. It worked perfectly when I used cdate on a string date.
LikeLike
ffs! why is everyone ignoring dd/MM/yyy!!
How?
LikeLike
How to format Parameters!Date.Value in yyyy-MM-dd format
LikeLike
Format(Parameters!Date.Value,“yyyy-MM-dd”)
LikeLike
just works with the last one
Format(Cdate(Parameters!Date.Value),”dd-MM-yyyy”)
🙂
thanks .,,
LikeLike
Thanks For the tip! this is what I was looking for!
LikeLike
Very usefull information for date conversion..
Thanks…
LikeLike
Thanks, this is how it should be. One needs help, he searches, and gets this solution straight to the point. useful … time-saving.
LikeLike
i have to put date in my report in form of mm/dd/yyyy.but i have value in database in form of 120314(12 is representing 2012,03 is representing march and 14 is representing date)
any help will be of great help.
LikeLike
Convert the value in the SQL query before returning to Reporting Services . This page is your reference : http://msdn.microsoft.com/en-us/library/ms187928.aspx
LikeLike
Thanks, exactly what i needed !!
LikeLike
I need to change the format of the date picker from datetime to date. Where do I enter the above formats? The report is based on a stored procedure.
LikeLike
when ckicking on the datepicker my date date should display like yyyy-mm-dd can u helip me out with that.
LikeLike
I need to display the date in the format like “April 3rd 2015”. Please let me know how I can achieve this.
LikeLike
I need following format:
April 1st, 2016
Or
April 2nd, 2016
Or
April 3rd, 2016
LikeLike
I want to change all ssrs reports date format to dd/MM/yyyy. I checked in the expression and found that format is fetched with rendering culture value. how can I achieve to change all report date format ?
Thanks.
LikeLiked by 1 person