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")
About these ads
Categories: Reporting Services
  1. Sergio
    November 15, 2007 at 1:39 pm

    There is another way to use the FormatDateTime command:
    FormatDateTime(Parameters!Date.Value, 0) –> 6/15/2007 6:20:07 PM
     

  2. Artem
    March 28, 2008 at 12:16 am

    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 ?

  3. Ahmed
    June 16, 2008 at 4:19 pm

    You certainly helped me. Thanks :)

  4. Trevor
    December 17, 2008 at 4:41 pm

    The Format command works great. Thanks for this.

  5. Javier
    January 30, 2009 at 5:44 pm

    Just what i need!

  6. Jack
    April 15, 2009 at 3:42 pm

    Thanks!

  7. Fabienne
    October 29, 2009 at 1:35 pm

    thank you, this is very useful. straight to the point.

  8. The
    April 15, 2010 at 3:58 pm

    Thanks, it\’s exactly what i need!!!

  9. Jag
    April 27, 2010 at 4:18 pm

    Solved my problem for me! Cheers buddy.

  10. Joe
    May 14, 2010 at 9:27 pm

    Thanks for the info, saved me time!

  11. Muneeb Noor
    October 7, 2010 at 7:44 am

    Thanks mate! Excellent solution. Saved my day!!! Thumbs Up

  12. Ron
    November 24, 2010 at 3:12 pm

    Thanks Buddy !!! Excellent stuff. Keep up the Good Work!!

  13. Mahendran
    February 7, 2011 at 7:55 am

    Thanks Buddy

  14. Tabithaaaaaa
    March 3, 2011 at 11:14 am

    Thanks from me, too…

  15. anil
    June 17, 2011 at 11:57 am

    its great !

  16. lijin
    November 16, 2011 at 6:42 am

    thanks…

  17. DebbieKidman
    November 24, 2011 at 6:12 pm

    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!

  18. Marcos Strapazon
    March 8, 2012 at 4:58 pm

    Thanks man, it was very useful.

  19. mw
    April 4, 2012 at 7:17 pm

    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

    • Naren
      May 14, 2012 at 8:42 am

      In Report parameters tab – > Ur Date Parameter -> Default Values -> =Today

  20. June 7, 2012 at 2:58 pm

    Thanks Naren !

  21. svk
    September 29, 2012 at 4:24 pm

    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

    • rahlquist
      July 12, 2013 at 6:33 pm

      Did you ever get this working SVK? IF so do you mind sharing how?

  22. Divakar
    October 12, 2012 at 4:12 am

    I need in “dd-MM-yyyy hh:mm:ss” format …. Could any one suggest please….

  23. Divakar
    October 12, 2012 at 4:17 am

    Divakar :
    I need in “dd-MM-yyyy hh:mm:ss AM/PM” format …. Could any one suggest please….

    • ChatDeDicte
      February 4, 2013 at 6:46 pm

      Format(Now,“dd-MM-yyyy hh:mm:ss tt”)

  24. Vani
    October 12, 2012 at 4:26 pm

    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.

  25. Johnny
    November 2, 2012 at 6:40 pm

    ffs! why is everyone ignoring dd/MM/yyy!!

    How?

  26. Shaheen
    November 26, 2012 at 8:00 am

    How to format Parameters!Date.Value in yyyy-MM-dd format

  27. March 11, 2013 at 8:47 am

    just works with the last one

    Format(Cdate(Parameters!Date.Value),”dd-MM-yyyy”)
    :)
    thanks .,,

  28. March 21, 2013 at 4:25 pm

    Thanks For the tip! this is what I was looking for!

  29. Naresh
    April 12, 2013 at 11:47 am

    Very usefull information for date conversion..

    Thanks…

  30. Will Anarna
    July 11, 2013 at 12:03 am

    Thanks, this is how it should be. One needs help, he searches, and gets this solution straight to the point. useful … time-saving.

  31. utk
    March 25, 2014 at 9:04 am

    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.

  32. Diwakar
    April 24, 2014 at 3:09 pm

    Thanks, exactly what i needed !!

  1. August 15, 2012 at 9:05 am
  2. August 15, 2012 at 9:57 am
  3. February 27, 2013 at 12:40 pm

Leave a Reply

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

Follow

Get every new post delivered to your Inbox.

Join 57 other followers

%d bloggers like this: