Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
RocketScientist
Official SQLTeam Chef
85 Posts |
 Posted - 2006-03-03 : 12:20:11
|
| Not sure that this is the correct forum or not, but I'll give it a go.I'm trying to write a formula for a report builder report. I'm having some issues getting any of the date formulas to work. For example, I have a table that contains a "CreationDate" and a "DispatchDate" column, and I want to get the difference between the two dates. So I use the DATEDIFF internal RB function, like this:Click on "Define Formula" in report builderType a name for the formula, "Time To Dispatch"In the "Formula for each Assignment" box, use the GUI to put this in:DATEDIFF(interval, "Creation Date", "Dispatch Date")You'll note that's not copy-paste. Report builder functions are not copy-pasteable. This will be a recurring theme.So, what do I put for interval? I've tried the SQL Server standard "mi" (without the quotes, and with the quotes), and that didn't work. So I try the online help for the datediff in report builder. It says as an example:DATEDIFF("month", #1/1/2009#, #3/31/2009#)First I try using "month" (with the quotes) No go. So, I copy/paste that from Help into Report Builder. Uh...no. Can't paste into report builder....so I type it in. The error message I'm getting is the same in all cases. Title is "Formula Error" with the text "The arguments to the following function are not valid: DATEDIFF". I get that error when I try to use the GUI to create a formula or if I type something in straight out of help. Any thoughts? I can repro the issuse with a simpler table, say:create table foo (i int identity(1,1) not null,startdate datetime not null, enddate datetime not null)Right now I'm thinking about adding a calculated column to the table so I can actually use the reporting engine to, you know, make reports. |
|
|
jhermiz
3564 Posts |
Posted - 2006-03-03 : 13:17:28
|
Why dont you in your query (SQL) in your procedure use DATEDIFF ?Also did you put an '=' symbol when making a function call ?=DateDiff(interval, date1, date2) ?Here is an example:=DateDiff("yyyy", CDate(Fields!DOB.Value ), DateTime.Today)
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
 |
|
|
RocketScientist
Official SQLTeam Chef
85 Posts |
Posted - 2006-03-03 : 14:14:29
|
| Why dont you in your query (SQL) in your procedure use DATEDIFF ?Because this is an end user tool that I'm testing. I'm not going near the business of teaching my end-users, who have basic understanding of Excel, about set theory. Tried the equal sign thing. Repeatedly. With many variations on the theme. Plus I can just drop, say, a constant into the formula bar (have a formula that's just the number 3, for example) and it works fine. Not =3, just 3. |
|
|
|
jhermiz
3564 Posts |
Posted - 2006-03-03 : 14:24:34
|
Im not sure I understand your problem.I tried the exact same in my report and it works perfectly fine ?
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
|
obsid
Starting Member
1 Post |
Posted - 2007-06-11 : 13:45:55
|
| The documentation by microsoft is incorrect. (http://msdn2.microsoft.com/en-us/library/aa337092(SQL.90).aspx)The proper formula to get it to work in report builder is:DATEDIFF("Month", #1/1/2009#, #3/31/2009#)Notice that it IS case sensative and it must be in " ". (unlike what the documentation says). |
|
|
|
|
|
|
|
|
Problem with Report Builder