次の方法で共有

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

  • Anonymous
    April 06, 2006
    online directory main

  • Anonymous
    January 18, 2007
    Hi=DateAdd(DateInterval.Day,9,Fields!CreationDate.Value)=DateAdd("Day",9,Fields!CreationDate.Value)Wont workRuvy

  • Anonymous
    January 18, 2007
    your musing is very helpfull=DateAdd("d",9,Fields!CreationDate.Value)WorksRuvy

  • Anonymous
    March 01, 2007
    The only one that works for me in my example is =DateAdd("d", 6, Today())How annoying you are Microsoft!

  • Anonymous
    March 31, 2008
    PingBack from http://collegefunfactsblog.info/russell-christophers-semi-useful-bi-musings-fun-with-date-functions/

  • Anonymous
    June 02, 2008
    I found this works:DateAdd("M", 3, SomeDateField)

  • Anonymous
    July 08, 2008
    =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"

  • Anonymous
    August 08, 2008
    PingBack from http://ssrslearningcurve.wordpress.com/2008/08/08/using-the-dateadd-function-in-ssrs/

  • Anonymous
    August 17, 2008
    The following works in SSRS 2005DateAdd(DateInterval.Month,1, Today)DateAdd(DateInterval.Minute,1, Today)

  • Anonymous
    January 08, 2009
    Hi,Id you want to Display EndDate for parameter=DateAdd(”s”,-1,DateAdd(”d”,1,Today()))for eaxample if are on day 01/01/2009it should display as 01/01/2009 11:59:00 PM

  • Anonymous
    April 17, 2009
    Thank you for "=DateAdd("d",9,Fields!CreationDate.Value)" - works perfectly

  • Anonymous
    July 28, 2009
    =DateAdd(DateInterval.Year,1,now()) works to add years....talk about dodgy coding.

  • Anonymous
    October 29, 2009
    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???

  • Anonymous
    October 29, 2009
    Format(Fields!LastPaidDate.Value, "dd/MM/yyyy")

  • Anonymous
    March 26, 2010
    thanks for the "d" and "M" for dateAdd... microsoft really needs to update their material

  • Anonymous
    May 06, 2010
    Thanks for the "dd", "MM", "yyyy" ... I dont know why MSFT is not updating their material...

  • Anonymous
    June 13, 2012
    how can we get a weekday name from date in ssrs. please help me

  • Anonymous
    July 17, 2012
    @Vijaya Thank you thank you thank you. I love you.

  • Anonymous
    September 11, 2012
    Nice article...