Dates difference in ssrs

Question

Post reply

Dates difference in ssrs

keywestfl9

SSCertifiable

Points: 5215
More actions
October 23, 2008 at 10:26 am

#214439

hello all
i am trying to find the date difference between two dates in ssrs.
=DateDiff("d",d1,d2)
it works if d2 is greater than d1
i want to do in any scenario where d1 might be null or greater than d2
or d2 can be null and d1 has value
i can some wierd number like 73331 if d1 is null or d2 is null
how do i do it?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

So what do you want for when:

2. d1 is null

3 d2 is null

Here is an idea:

IIF(d1 is nothing or d2 is nothing, -1, Abs(DateDiff("d", d1, d2)))

This will return -1 if either value is null and the absolute value otherwise.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

keywestfl9 SSCertifiable Points: 5215 More actions · Answer 2

Thanks that worked. i used "" in case of -1 as i have a column called # of days which actually has this express of datediff

i have one more problem, i am doing an average of the # of days field at the table footer, i dont know how to do it

Please help me

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Probably the easiest way to do it is to create a calculated field in your dataset using the expression you already have and then putting it in the report both the detail and the average. Depending on how you want to handle your special cases in the average.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

keywestfl9 SSCertifiable Points: 5215 More actions · Answer 4

thanks again

i tried thru calculated field (dataset) and since certain rows contain null value and when i try to do the average on the calculated field AVG(calculated field) i am getting #Error

i want to exclude the null value and only do average on the field which has value.

Can you please suggest that?

Jerry Hung SSChampion Points: 12968 More actions · Answer 5

If possible, I may even prefer to do it in the T-SQL at times

Calculated column in the Table, or Calculated field in the SSRS Dataset both would work

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

How do you want to handle null/invalid values in the average? Do you want to ignore them or count them as 0? If you have the following 3 values which average would you want?

2, Null, 4

Average replacing null with 0: 2

Average ignoring null: 3

To get answer 1 you would do something like this:

=Avg(IIF(d1 is nothing or d2 is nothing, 0, Abs(DateDiff("d", d1, d2))))

To get answer 2 you could do something like this:

=SUM(IIF(d1 is nothing or d2 is nothing, 0, Abs(DateDiff("d", d1, d2))))/

SUM(IIF(d1 is nothing or d2 is nothing, 0, 1))

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question