DATEDIFF with cutoff time

Question

I'm working on a Room Scheduling application. We have this Room Check Out Rule that we need follow. All room check out should be 12:00 PM. If the check out date is after 12.00 PM it will be considered additional 1 day.

Below is my T-SQL code that returns 5 days.

  SELECT DATEDIFF(day, '3/12/2013 12:00:00 PM', '3/17/2013 3:00:00 PM');

If you see the code above the end date is 3:00:00 PM. How can I tweak this code to return 6 days instead of 5?

What if I have this code?

  SELECT CEILING(DATEDIFF(SECOND, '3/12/2013 02:00:00 PM' , '3/17/2013 12:50:36 PM') / (24.0 * 60 * 60))

The above code still returns 5 days instead of 6.

12:00 PM is Noon not Midnight. Have you stated the problem correctly? — Pieter Geerkens, Mar 11 '13 at 17:13
Yes it 12:00PM Noon time. The rule is Check Out Time should always be 12:00PM. — user2059064, Mar 11 '13 at 18:30
The start date is not important. The company rule is all room check out above 12:00 PM must add 1 day. I know it's weird but that's the rule they gave me. :-( — user2059064, Mar 11 '13 at 18:41
my bad. In order for me to have consistent results I decided to put 12:00 PM in the start date. Thanks. — user2059064, Mar 11 '13 at 20:33

score 3 · Answer 1 · answered Mar 11 '13 at 17:12

3

SELECT CEILING(DATEDIFF(SECOND, '3/12/2013 12:00:00 PM', '3/17/2013 12:00:01 PM') / (24.0 * 60 * 60))

answered Mar 11 '13 at 17:12

polybios

1,159
8
20

Fastastic! Thanks @polybios – user2059064 Mar 11 '13 at 17:14
1

@user2059064 Count hours and divide to 24. – Hamlet Hakobyan Mar 11 '13 at 17:15
@polybios CEILING what is main use of this – A.Goutam Mar 11 '13 at 17:17
@A.Goutam read the docs, man. http://msdn.microsoft.com/en-us/library/ms189818.aspx – Aaron Bertrand Mar 11 '13 at 17:25
Thanks for the code @polybios. I now have a solution. I'll just put a standard start date with 12:00 PM time to get consistent results. – user2059064 Mar 11 '13 at 18:47

score 2 · Accepted Answer · answered Mar 11 '13 at 18:42

The correct way is to subtract 12 hours from StartDate and EndDate, then take a day-diff + 1:

declare @dateStart as datetime, @dateEnd as datetime
set @dateStart = cast('20130301 11:59:59 AM' as datetime)
set @dateEnd   = cast('20130301 12:01:01 PM' as datetime)

select 
  @dateStart, 
  @dateEnd

select days = 1 + datediff(d,@dateStart,@dateEnd)

select 
  days = 1 + datediff(d, dateadd(hh, -12, @dateStart), dateadd(hh, -12, @dateEnd))

returns this:

----------------------- -----------------------
2013-03-01 11:59:59.000 2013-03-01 12:01:01.000

days
-----------
1

days
-----------
2

Clearly the second formula is correct, not the first.

score 1 · Answer 3 · answered Mar 11 '13 at 17:14

1

Perhaps you can count hours:

SELECT DATEDIFF(hour, '3/12/2013 12:00:00 PM', '3/17/2013 3:00:00 PM');

Therefore, 123 > 120 (or divided by 24 - 5.125 > 5) accounts for 6 days.

answered Mar 11 '13 at 17:14

DATEDIFF with cutoff time

3 Answers3