Convert Time In sql
hi in my table i have 3 column (name – from – to)
and data type time(7)
i insert 3 shift
shift1 from 08:00:00 to 15:00:00
shift2 from 15:00:00 to 22:00:00
shift3 from 22:00:00 to 08:00:00
and i Has Create SP to check of what is shift now
this is the SP
create PROC SelectShift AS DECLARE @TimeNow TIME = substring(convert(varchar(20), GetDate(), 9), 13, 5) + ' ' + substring(convert(varchar(30), GetDate(), 9), 25, 2) SELECT s.Shift_Name, s.Id FROM Schedule s WHERE @TimeNow BETWEEN s.ShiftTime_From AND s.ShiftTime_To
it work fine in the shift 1 , 2
but in shift 3 it select null (time now 22:55:00.0000000)
i don’t use sql time before so can u help
2 Solutions collect form web for “Convert Time In sql”
The results you are getting are correct as there is no way for the time variable to determine if the begining of the shift was the day before (i.e. before 00:00:00) or after.
You can work around this by using the following method but it is quite ugly coding.
DECLARE @TimeNow TIME = '22:55:00.0000000' -- substring(convert(varchar(20), GetDate(), 9), 13, 5) --+ ' ' + substring(convert(varchar(30), GetDate(), 9), 25, 2) SELECT s.Shift_Name FROM #Schedule s WHERE @TimeNow BETWEEN s.ShiftTime_From AND (CASE WHEN s.ShiftTime_From > s.ShiftTime_To THEN '23:59:59' ELSE s.ShiftTime_To END)
My first thought was to shift each interval to consider it starting from 00:00:00 and shift the current time by the same amount, but subtracting times isn’t supported. So I ended up using two cases.
This could be a table valued function rather than a proc, which might make composing it eaiser:
Create Proc ShiftForTime @time time as Select * From schedule Where ( ShiftTime_From < ShiftTime_To and @time >= ShiftTime_From and @time < ShiftTime_To ) or ( ShiftTime_From > ShiftTime_To and ( @time >= ShiftTime_From or @time < ShiftTime_To ) ) Go Create Proc SelectShift As Declare @now time = getdate() Exec ShiftForTime @now
Here’s an example using a table valued function, with a bunch of different tests:
Just for intellectual curiosity, my first thought can be made to work, although it might be less efficient
Create Proc ShiftForTime2 @time time as Select * From schedule Where dateadd(second, datediff(second, ShiftTime_From, 0), @time) < dateadd(second, datediff(second, ShiftTime_From, 0), ShiftTime_To)