CODEWITHSUNDEEP
sql-servert-sqldatetimestored-procedurestime
kiro karam
kiro karam

Reputation: 99

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

Upvotes: 1

Views: 156

Answers (2)

Laurence
Laurence

Reputation: 10976

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:

Example SQLFiddle

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)

Upvotes: 1

Fuzzy
Fuzzy

Reputation: 3810

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)

Upvotes: 1

Related Questions