CODEWITHSUNDEEP
sqlsql-servert-sqldatetime
reva
reva

Reputation: 1477

Days Unavailable

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

Upvotes: 2

Views: 707

Answers (2)

Joel Spolsky
Joel Spolsky

Reputation: 33667

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.

Upvotes: 7

gbn
gbn

Reputation: 432471

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

Upvotes: 4

Related Questions