Welcome to MSDN Blogs Sign in | Join | Help

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
Published Tuesday, September 13, 2005 12:31 PM by russch

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

# DateAdd in Report Builder 2005

Friday, March 17, 2006 10:19 PM by Ingrid Fields

# hnnrwxd@hotmail.com

Thursday, April 06, 2006 11:52 AM by hnnrwxd@hotmail.com
online directory main

# re: Fun with Date Functions in SQL Reporting Services Report Builder

Thursday, January 18, 2007 10:39 AM by Ruvy

Hi

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

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

Wont work

Ruvy

# re: Fun with Date Functions in SQL Reporting Services Report Builder

Thursday, January 18, 2007 10:43 AM by Ruvy

your musing is very helpfull

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

Works

Ruvy

# re: Fun with Date Functions in SQL Reporting Services Report Builder

Thursday, March 01, 2007 10:56 AM by Rob

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

How annoying you are Microsoft!

# College Fun Facts » Russell Christopher’s Semi-Useful BI Musings : Fun with Date Functions …

Monday, March 31, 2008 4:40 PM by College Fun Facts » Russell Christopher’s Semi-Useful BI Musings : Fun with Date Functions …

PingBack from http://collegefunfactsblog.info/russell-christophers-semi-useful-bi-musings-fun-with-date-functions/

# re: Fun with Date Functions in SQL Reporting Services Report Builder

Monday, June 02, 2008 9:14 AM by Nathan

I found this works:

DateAdd("M", 3, SomeDateField)

# re: Fun with Date Functions in SQL Reporting Services Report Builder

Tuesday, July 08, 2008 10:58 AM by deepu

=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"

# Using the DateAdd() Function in SSRS « SSRS Learning Curve

Friday, August 08, 2008 11:44 AM by Using the DateAdd() Function in SSRS « SSRS Learning Curve

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

# re: Fun with Date Functions in SQL Reporting Services Report Builder

Monday, August 18, 2008 5:01 AM by Vijaya Krishna Paruchuri

The following works in SSRS 2005

DateAdd(DateInterval.Month,1, Today)

DateAdd(DateInterval.Minute,1, Today)

# re: Fun with Date Functions in SQL Reporting Services Report Builder

Thursday, January 08, 2009 2:08 PM by Ramana

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

# re: Fun with Date Functions in SQL Reporting Services Report Builder

Friday, April 17, 2009 7:03 PM by Joe

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

Leave a Comment

(required) 
required 
(required) 
 
Page view tracker