Fun with Date Functions in SQL Reporting Services Report Builder



Alec Harley reported an interesting (read: annoying) behavior around the use of Dateparts inside date-related functions used by Report Builder


In older builds, it appears you may not use the nice abbreviated Dateparts that we’re used to (for example “dd” instead of “Day”). If you do, Report Builder throws the following unhandled exception:



“Operation is not valid due to the current state of the object.”


After digging around I found that right now we must use the un-abbreviated Datepart, AND it must be capitalized correctly or we’ll see the error:


DateAdd(“mm”, 3, SomeDateField) // Will blow up with the message above


DateAdd(“Month”, 3, SomeDateField) // Will work!


DateAdd(“month”, 3, SomeDateField) // Explodes with the same error message

Comments (20)

  1. Ruvy says:

    Hi

    =DateAdd(DateInterval.Day,9,Fields!CreationDate.Value)

    =DateAdd("Day",9,Fields!CreationDate.Value)

    Wont work

    Ruvy

  2. Ruvy says:

    your musing is very helpfull

    =DateAdd("d",9,Fields!CreationDate.Value)

    Works

    Ruvy

  3. Rob says:

    The only one that works for me in my example is =DateAdd("d", 6, Today())

    How annoying you are Microsoft!

  4. Nathan says:

    I found this works:

    DateAdd("M", 3, SomeDateField)

  5. deepu says:

    =iif(Parameters!start_date.Value="","No date"datystem.DateTime.ParseExact(Parameters!start_date.Value,"MM/dd/yyyy",System.Globalization.DateTimeFormatInfo.InvariantInfo).ToString("dd/MM/yyyy")) is this a valid statement ?

    Will No date gets printed if I give a blank pera meter..Plase can any one help me in this…The problem here is if i give date…The date gets converted and printed in dd/mm/yy formate …in case i if i  did not enter parameter i get Get an error message Instead of "No date"

  6. Vijaya Krishna Paruchuri says:

    The following works in SSRS 2005

    DateAdd(DateInterval.Month,1, Today)

    DateAdd(DateInterval.Minute,1, Today)

  7. Ramana says:

    Hi,

    Id you want to Display EndDate for parameter

    =DateAdd(”s”,-1,DateAdd(”d”,1,Today()))

    for eaxample if are on day 01/01/2009

    it should display as 01/01/2009 11:59:00 PM

  8. Joe says:

    Thank you for "=DateAdd("d",9,Fields!CreationDate.Value)" – works perfectly

  9. Jason Wicks says:

    =DateAdd(DateInterval.Year,1,now()) works to add years….talk about dodgy coding.

  10. Bernard says:

    Hi,

    My field in my report is like this

    =Fields!LastPaidDate.Value

    And displays on the page like this:

    9/10/2029 12:00:00 AM

    And I need to display it like this:

    9/10/2029

    Any Ideas???

  11. Ian Gregson says:

    Format(Fields!LastPaidDate.Value, "dd/MM/yyyy")

  12. Kim-Lien says:

    thanks for the "d" and "M" for dateAdd… microsoft really needs to update their material

  13. Vankayala says:

    Thanks for the "dd", "MM", "yyyy" … I dont know why MSFT is not updating their material…

  14. khan says:

    how can we get a weekday name from date in ssrs. please help me

  15. Peanut says:

    @Vijaya

    Thank you thank you thank you. I love you.