Days Unavailable

Question

I need a simple SQL to accomplish the below:

Problem:

When a petrol bunk runs out of fuel, the admin makes note of the DateTime (RunOutDate) when it ran out of fuel and notes also the DateTime (ResupplyDate) when the fuel supply was back on.

I need to create a report on how many days the bunk ran out of fuel.

eg.

1/1/1 10:10 to 1/1/1 10:50 should be counted as 1

1/1/1 10:10 to 2/1/1 07:20 should be counted as 2

1/1/1 23:55 to 2/1/1 00:10 should be counted as 2

I can not bank using hours using DateDiff as 24 hours could have spanned across 2 days.

TIA

Answer 1

DATEDIFF using day, then add 1.

DATEDIFF uses the midnight to count days so you'll get 0, 1, 1 for each example above. Then add 1.

DATEDIFF(day, '16 Dec 2008 10:10', '16 Dec 2008 10:50') + 1

Answer 2

DATEDIFF(d, RunOutDate, ResupplyDate) + 1

Remember that DATEDIFF always counts the number of BOUNDARIES that you cross. For days (first argument d), it counts the number of times the clock passed midnight. So to count the number of days covered you just add 1.