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.

 All Forums
 SQL Server 2005 Forums
 Analysis Server and Reporting Services (2005)
 DateDiff Function

Author  Topic 

Peace2007
Posting Yak Master

239 Posts
Posted - 2008-09-06 : 05:21:42
Hi,

I need the difference of two date values in day. I've tried to calculate it as follows: 
=DateDiff("day", First(Fields!StartDate.Value, "SE_DataSource"),First(Fields!DueDate.Value, "SE_DataSource"))

But when I run the report, it displays #ERROR!

Any idea?

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-09-06 : 06:19:25
Tried with no double quote for interval parameter?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Peace2007
Posting Yak Master

239 Posts
Posted - 2008-09-06 : 06:33:40
yes it gives following error:

Error	1	[rsCompilerErrorInExpression] The Value expression for the textbox ‘textbox39’ contains an error: 
[BC30455] Argument not specified for parameter 
'DateValue' of 'Public Function Day(DateValue As Date) As Integer'.
e:\sql-investigation\seprjreport\seprjreport\Project Profile.rdl	0	0
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-09-06 : 10:37:36
Ok.
Are the values for the two dates valid? They are not NULL?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Peace2007
Posting Yak Master

239 Posts
Posted - 2008-09-07 : 00:22:11
yes That's the point
They're sometimes NULL; but how I can handle them in this situation?!
Besides, how it recognizes NULLS while building report?!!

Thanks for the replies Peso
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-09-07 : 00:25:23
quote:
Originally posted by Peace2007

yes That's the point :)
They're sometimes NULL; but how I can handle them in this situation?!

Thanks for the replies Peso

why are you using First()? that just takes first value that comes in your dataset. can you explain what your requirement is?
Go to Top of Page

Peace2007
Posting Yak Master

239 Posts
Posted - 2008-09-07 : 00:29:37
First has come when I've chosen the field from data set fields.
I'm having more than 100 records each containing two date fields (of course, as well as other columns) I need to display the duration between those two date values in days. For some records one of those dates may be NULL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-09-07 : 00:42:10
quote:
Originally posted by Peace2007

First has come when I've chosen the field from data set fields.
I'm having more than 100 records each containing two date fields (of course, as well as other columns) I need to display the duration between those two date values in days. For some records one of those dates may be NULL

are these fields in the dataset which is linked to your table/container? then you shouldnt be using First. just use

=DateDiff("day",IIF(Len(Fields!StartDate.Value)>0,CDate(Fields!StartDate.Value),CDate("1 Jan 1900")),IIF(Len(Fields!DueDate.Value)>0,CDate(Fields!DueDate.Value),CDate("1 Jan 1900")))
Go to Top of Page

Peace2007
Posting Yak Master

239 Posts
Posted - 2008-09-07 : 01:06:33
quote:
Originally posted by visakh16


are these fields in the dataset which is linked to your table/container? then you shouldnt be using First. just use

=DateDiff("day",IIF(Len(Fields!StartDate.Value)>0,CDate(Fields!StartDate.Value),CDate("1 Jan 1900")),IIF(Len(Fields!DueDate.Value)>0,CDate(Fields!DueDate.Value),CDate("1 Jan 1900")))


yes, They're in Dataset. However I used your formula and it displays #Error for these values: 
Start Date: 9/1/2007 12:00:00AM
Due Date: 10/1/2007 12:00:00AM
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-09-07 : 02:31:52
quote:
Originally posted by Peace2007

quote:
Originally posted by visakh16


are these fields in the dataset which is linked to your table/container? then you shouldnt be using First. just use

=DateDiff("day",IIF(Len(Fields!StartDate.Value)>0,CDate(Fields!StartDate.Value),CDate("1 Jan 1900")),IIF(Len(Fields!DueDate.Value)>0,CDate(Fields!DueDate.Value),CDate("1 Jan 1900")))


yes, They're in Dataset. However I used your formula and it displays #Error for these values: 
Start Date: 9/1/2007 12:00:00AM
Due Date: 10/1/2007 12:00:00AM


Are these fields of datetime type in table?
Go to Top of Page

Peace2007
Posting Yak Master

239 Posts
Posted - 2008-09-07 : 02:40:05
quote:

Are these fields of datetime type in table?


yes
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-09-07 : 02:45:24
quote:
Originally posted by Peace2007

quote:

Are these fields of datetime type in table?


yes

And is your container (table/matrix/list) linked to SE_DataSource dataset ?
Go to Top of Page

Peace2007
Posting Yak Master

239 Posts
Posted - 2008-09-07 : 03:19:20
my container is a text box ( I've filtered the report on one of the records), so it's not linked to any specific data set itself. However, I'm choosing the date fields from SE_DataSource, that's why first is chosen. SE_DataSource is filtered on one record ID.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-09-07 : 04:21:26
quote:
Originally posted by Peace2007

my container is a text box ( I've filtered the report on one of the records), so it's not linked to any specific data set itself. However, I'm choosing the date fields from SE_DataSource, that's why first is chosen. SE_DataSource is filtered on one record ID.



so what exactly are you trying to get in textbox. obviously your dataset will have more than a single row. which rows datediff are you trying to retrieve/
Go to Top of Page

Peace2007
Posting Yak Master

239 Posts
Posted - 2008-09-07 : 06:22:43
my dataset returns only one value :) because the ID, by which I've filtered my dataset on it is unique, as I show value of other fields in other textboxes
Let me explain it well. I have a table in report1, which displays list of projects. when user clicks on a table row I pass the project ID to report2 and filter the dataset in report2 on the selected project ID. Then in report2 I display information of the selected project in text boxes. Now I need to display the number of days between start date and end date of the selected project in the 2nd report.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-09-07 : 07:24:08
quote:
Originally posted by Peace2007

my dataset returns only one value :) because the ID, by which I've filtered my dataset on it is unique, as I show value of other fields in other textboxes
Let me explain it well. I have a table in report1, which displays list of projects. when user clicks on a table row I pass the project ID to report2 and filter the dataset in report2 on the selected project ID. Then in report2 I display information of the selected project in text boxes. Now I need to display the number of days between start date and end date of the selected project in the 2nd report.

ok then do like this

=DateDiff("day",IIF(Len(First(Fields!StartDate.Value,"SE_DataSource"))>0,CDate(First(Fields!StartDate.Value,"SE_DataSource")),CDate("1 Jan 1900")),IIF(Len(First(Fields!DueDate.Value,"SE_DataSource"))>0,CDate(First(Fields!DueDate.Value,"SE_DataSource")),CDate("1 Jan 1900")))
Go to Top of Page

Peace2007
Posting Yak Master

239 Posts
Posted - 2008-09-08 : 06:31:34
It gives #Error again! I modified the dataset query and added datediff there

Thanks Visakh
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site