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
There is another way to use the FormatDateTime command:
FormatDateTime(Parameters!Date.Value, 0) –> 6/15/2007 6:20:07 PM
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 ?
You certainly helped me. Thanks
The Format command works great. Thanks for this.
Just what i need!
Thanks!
thank you, this is very useful. straight to the point.
Thanks, it\’s exactly what i need!!!
Solved my problem for me! Cheers buddy.
Thanks for the info, saved me time!
Thanks mate! Excellent solution. Saved my day!!! Thumbs Up
Thanks Buddy !!! Excellent stuff. Keep up the Good Work!!
Thanks Buddy
Thanks from me, too…
its great !
thanks…
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!
Thanks man, it was very useful.
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
In Report parameters tab – > Ur Date Parameter -> Default Values -> =Today
Thanks Naren !
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
I need in “dd-MM-yyyy hh:mm:ss” format …. Could any one suggest please….
Format(Now,“dd-MM-yyyy hh:mm:ss tt”)
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.
ffs! why is everyone ignoring dd/MM/yyy!!
How?
How to format Parameters!Date.Value in yyyy-MM-dd format
just works with the last one
Format(Cdate(Parameters!Date.Value),”dd-MM-yyyy”)
thanks .,,
Thanks For the tip! this is what I was looking for!
Very usefull information for date conversion..
Thanks…