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

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

  • How to set timeout for a stored procedure in SQL Server
  • SQL Merge replication : How to tell what has changed.
  • SQL Server: how to add case statement to select
  • SQL to find discrepancy between 2 tables with same identifier
  • SQL Server - Selecting a Record With MAX Value
  • CSV FormatFile for OPENROWSET BULK catering for both plain COMMA and “COMMA”
  • Can't gain access to my database created though Visual Studio?
  • Preserving ORDER BY in SELECT INTO
  • Connection string between and SQL Server
  • T-Sql Multiple DateDiff with Multiple Start and End Dates Stored in the Same Table?
  • MDX: IIf condition on the value of a dimension is always false
  • How to display data in mail in proper format from Sql server database table?
  • 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
    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
    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
        dateadd(second, datediff(second, ShiftTime_From, 0), @time) 
          < dateadd(second, datediff(second, ShiftTime_From, 0), ShiftTime_To)
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.