Scenario:
Start DateTime = 01/01/2012 09:00
End DateTime = 02/01/2012 13:00
Working hours are 9-5 so this is closest to 1.5 days.
Start DateTime = 01/01/2012 09:00
End DateTime = 01/01/2012 17:00
This shows 1 day.
Start DateTime = 01/01/2012 09:00
End DateTime = 02/01/2012 16:00
This shows 2 days.
My current logic is this:
SELECT (CEILING(CAST(DATEDIFF(hh,@Startdate,@Enddate) AS DECIMAL) / 12)) / 2
The above works fine for values over 1 day. The problem is with start and ends on the same day show a half day. Ideally I would be able to only count the working parts of the day and not the full 24 hours.
Any help on this would be much appreciated.
Thanks,
Slobad23
Start DateTime = 01/01/2012 09:00
End DateTime = 02/01/2012 13:00
Working hours are 9-5 so this is closest to 1.5 days.
Start DateTime = 01/01/2012 09:00
End DateTime = 01/01/2012 17:00
This shows 1 day.
Start DateTime = 01/01/2012 09:00
End DateTime = 02/01/2012 16:00
This shows 2 days.
My current logic is this:
SELECT (CEILING(CAST(DATEDIFF(hh,@Startdate,@Enddate) AS DECIMAL) / 12)) / 2
The above works fine for values over 1 day. The problem is with start and ends on the same day show a half day. Ideally I would be able to only count the working parts of the day and not the full 24 hours.
Any help on this would be much appreciated.
Thanks,
Slobad23