April 10
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") |
Tools 
Help
