ansaurus

Question

Date difference in SQL Server 2005.

Answer 1

+1 A:

case when DateDiff(Day, 0, (PaidDate-DueDate-1)) <0 then 0 else
DateDiff(Day, 0, (PaidDate-DueDate-1))  end

josephj1989 2010-06-27 10:37:28

Since this does the same calculation twice, I think Guffa's solution suits me little better. Thanks for you help.

Rabin 2010-06-27 10:53:55

Answer 2

+4 A:

First off, that's not how you use DateDiff. This is how you use it:

DateDiff(Day, PaidDate, DueDate)

To get a zero value, just compare the dates:

case when PaidDate > DueDate then 0 else DateDiff(Day, PaidDate, DueDate) end

(I'm not sure however which date you want to normally be the later one, i.e. if you want to calculate the time left or the time over due, so you might need to swap them around. As it stands now it calculates the time left.)

Guffa 2010-06-27 10:39:05

Oh wow, thanks for your help Guffa.

Rabin 2010-06-27 10:54:17

I read somewhere that doing DateDiff(Day, 0, PaidDate-DueDate) gives a better accuracy. And the -1 in DueDate is just because I need not calculate for the day of the payment, ie calculating up until the day before day of payment. Once again thank you.

Rabin 2010-06-27 10:59:59

@Rabin: If you (mis)use DateDiff that way it only gives better accuracy if you have a time component also that you want to take into account, i.e. the difference between '2010-06-27 23:00' and '2010-06-28 01:00' would be zero as it's less than 24 hours, while DateDiff would give the result one as it's different dates. So, it's only more accurate if that is actually the result that you want. However, if you want higher resolution, just use a smaller unit than `day` instead of using DateDiff merely as a way to round a number.

Guffa 2010-06-27 14:16:03

Oh, so if I do not plan to use the time component, it's better not to do DateDiff(Day, 0, DueDate-PaidDate) but rather DateDiff(Day, PaidDate, DueDate)?

Rabin 2010-06-28 06:18:45

ansaurus

tags:

views:

answers:

Date difference in SQL Server 2005.

related questions