DATEDIFF in Report Builder

I'm having a bit of trouble with the DATEDIFF function in my Report Model project and in Report Builder.

I am trying to create a new Expression field that will work out a persons age from the current date and their Date of Birth, here is the formula that I have entered,

DATEDIFF("y", NOW(), DOB)

where DOB is the field that holds the persons date of birth in the database.

When I enter this into the formula box and click OK i get the following error,

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

The detailed error text is

Program Location:

at Microsoft.ReportingServices.Modeling.Expression.GetResultType()
at Microsoft.ReportingServices.ModelDesigner.ModelDesignerControl.m_ListNoItemExpression_Click(Object sender, EventArgs e)
at Microsoft.ReportingServices.ModelDesigner.ProjectModelView.Microsoft.DataWarehouse.Interfaces.ICommandTarget.InvokeCommand(MenuCommand menuCommand)
at Microsoft.DataWarehouse.VsIntegration.Designer.Host.CommandTargetMenuService.InvokeCommand(CommandID commandID)

This error ocurrs both in a Report Model project and also in report builder if I try to create a new field after the model has been deployed.

This also happens for DATEADD as well, I'm thinking it may be a problem with the first parameter but I can't find any reference on how to use this Function properly. Has anyone got either of these working in report builder, even if you don't could you post here to let me know you have the same problem.

Would be good to know i'm not alone in this.

Thanks|||I still really need help on this one.

If someone could load up Report Builder and try to create a new field using the DATEDIFF function this would really help me out.

If you get it working then i'll be able to take that onboard, if you don't get it working then I know i'm not alone.

Any help is appreciated|||

Report Builder? You mean Report Designer?
Why don't you get the age from the Query instead of getting it as a new field in the Report Desinger? I mean, add a calculated field directly to the Query, somehting like

SELECT [whatever you actually have], DATEDIFF(YEAR, GETDATE(), DOB) AS age
[and the rest of your query]

Or maybe I didn't understand your question

|||"When I enter this into the formula box"

Are you creating a field in the Repor Designer?
I wouldn't use NOW(), but Globals!ExecutionTime|||This issue has now been resolved, it was not occurring in Report Designer, it was occuring in Report Builder.

The DATEDIFF function in Report Builder must use the "long" names for the Interval and these must be capatalized.

E.g
"Day" - Will work
"dd" - Won't work
"day" - Won't work|||I have a similar question.

I can't seem to get the DATEDIFF function to work. I am trying to display the date from seven days prior to now.

My textbox has the value of...

=format(dateadd(Day, -7, Globals!ExecutionTime), "M/d")

and the error I get is...

Argument not specified for parameter 'DateValue' of Public Function Day(DateValue as Date) as Integer.

I just can't get my head around this one, and I'm sure it's simple. ANY help would be appreciated!|||Sorry, I meant to say I can't get the DateAdd function to work.|||

Day is a function, so it's expecting a parameter (a date value). I'd try to get that value from the query, not from a formula in a Text Box.
Maybe it's not the best solution, but I'd create a Dataset called DataSet1wkago with this query string:

SELECT DATEADD(DAY, -7, GETDATE()) AS last_week

And in the textbox I'd write

=First(Fields!last_week, "DataSet1wkago")

Again: this could be not the best solution, but it works.
I hope it helps you. Regards

|||Thanks, it's not the best solution, but it is still a solution!|||If you are trying to use this function in Report Builder then you will have to enclose the Interval in quotes e.g.

DATEADD("Day", -7, Globals!ExecutionTime)|||

I have the same problems with the data interval.

=Datediff("Day",Parameters!fromdate.Value,Now())

That I am using in a field in a Reporting Services report. I can not use the datediff in the query as I am using a parameter formattet at datetime.

Any suggestions on how this work or where to find useful documentation on expressions in Report Designer?

Thomas Black

|||

Hi,

try using "d" instead of "Day":

=Datediff("d", Parameters!fromdate.Value, now())

below are the list of 'code':

Setting	Description
yyyy	Year
q	Quarter
m	Month
y	Day of year
d	Day
w	Weekday
ww	Week of year
h	Hour
n	Minute
s	Second