December 10, 2015 at 3:26 am
Hi,
I currently have a view which shows has two columns, start time and end time.
I want to be able to work out the hours work i.e
Start time:09:00
End time :17:00
Answer = 7 hours (1 hour for lunch)
Is this possible?
thank you 🙂
December 10, 2015 at 3:33 am
Have you looked at the DATEDIFF function?
John
December 10, 2015 at 3:39 am
Hi,
How do I use this function? (sorry quiet new to SQL so still learning).
Also would it matter that it is a TIME datatype rather then DateTime.
Thank you for your help.
December 10, 2015 at 3:46 am
The quickest way to find out is to type DATEDIFF into your favourite search engine. Yes, it works with the time data type. Here's an example; I'll leave it to you to subtract the hour for lunch:
DECLARE @s time = '09:00', @f time = '17:00'
SELECT DATEDIFF(HOUR,@s,@f)
John
December 10, 2015 at 3:52 am
That is perfect.
thank you so much for your help 🙂
December 10, 2015 at 4:49 am
Using the above function is there a way to do an IF statement with it. As i am dealing with a call center that work different hours.
0900-1700
0900-1500
0930-1730
1200-1700
1300-1600
2115-0015
1630-0115
0045-0915
thank you
December 10, 2015 at 4:57 am
Sounds like you need a CASE expression, although you don't give many details. What are your expected results?
John
December 10, 2015 at 5:01 am
Hi,
thank you for your reply.
I have a start roster date however if someone works 21:00 - 06:00 then I need the date date to change to the next day.
i.e 10-12-2015 scheduled to work 2100-0600.
thanks
December 10, 2015 at 5:11 am
OK, the simplest way would be to do the calculation as normal, and use your CASE expression to test whether start is greater than end. If it is, then leave as is. If it isn't, then add the result (which will be negative or 0) to 24. That's the simplest way - whether it will be robust enough to handle all use cases isn't clear from the information you've given.
John
December 10, 2015 at 5:24 am
hazeleyre_23 (12/10/2015)
Hi,thank you for your reply.
I have a start roster date however if someone works 21:00 - 06:00 then I need the date date to change to the next day.
i.e 10-12-2015 scheduled to work 2100-0600.
thanks
WITH MyView AS ( -- sample data
SELECT * FROM (VALUES
('09:00', '17:00'),
('09:00', '15:00'),
('09:30', '17:30'),
('12:00', '17:00'),
('13:00', '16:00'),
('21:15', '00:15'),
('16:30', '01:15'),
('00:45', '09:15')
) d (StartTime, EndTime)
)
-- solution: if the end time is less than the start time, then add a day to it:
SELECT *
FROM MyView
CROSS APPLY (
SELECT q = DATEDIFF(
HOUR,
StartTime,
CASE WHEN EndTime < StartTime THEN DATEADD(DAY,1,EndTime) ELSE EndTime END)
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 10, 2015 at 5:45 am
Careful, Chris. That doesn't work if you use values explicitly defined as time.
DECLARE @TimeTable table (StartTime time, EndTime time)
INSERT INTO @TimeTable
SELECT * FROM (VALUES
('09:00', '17:00'),
('09:00', '15:00'),
('09:30', '17:30'),
('12:00', '17:00'),
('13:00', '16:00'),
('21:15', '00:15'),
('16:30', '01:15'),
('00:45', '09:15')
) d (StartTime, EndTime)
SELECT *
FROM @TimeTable
CROSS APPLY (
SELECT q = DATEDIFF(
HOUR,
StartTime,
CASE WHEN EndTime < StartTime THEN DATEADD(DAY,1,EndTime) ELSE EndTime END)
) x
Returns values for the first five rows, but then:
Msg 9810, Level 16, State 1, Line 176
The datepart day is not supported by date function dateadd for data type time.
John
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply