Check if a time is between two times time DataType

Question

I have a table that has a column  Created  as a datetime   I m trying to query to check if the time for the Created value is between two times   The Created datetime for the first row is  2013-07-01 00 00 00 000   Midnight  and I m trying to query for items with a time between 11PM and 7AM   select   from MyTable where CAST Created as time  between  23 00 00  and  06 59 59    But no results are returned   Do I need to convert my times to datetimes

User · Answer

select   from MyTable where CAST Created as time  not between  07 00  and  22 59 59 997

User · Answer

WITH CTE as   SELECT CAST ShiftStart AS DATETIME  AS ShiftStart   CASE WHEN ShiftStart  gt  ShiftEnd THEN CAST ShiftEnd AS DATETIME   1 ELSE CAST ShiftEnd AS DATETIME  END AS ShiftEnd FROM   TABLE NAME     SELECT   FROM CTE WHERE  CAST  11 00 00  AS DATETIME  BETWEEN ShiftStart AND ShiftEnd -- Start of Shift OR CAST  23 00 00  AS DATETIME  BETWEEN ShiftStart AND ShiftEnd -- End of Shift

User · Answer

I suspect you want to check that it s after 11pm or before 7am   select   from MyTable where CAST Created as time   gt    23 00 00      or CAST Created as time   lt   07 00 00

User · Answer

I had a very similar problem and want to share my solution  Given this table  all MySQL 5 6    create table DailySchedule     id         int auto increment primary key    start time time not null    stop time  time not null      Select all rows where a given time x  hh mm ss  is between start and stop time  Including the next day   Note  replace NOW   with the any time x you like  SELECT id FROM DailySchedule WHERE    start time  lt  stop time AND NOW   BETWEEN start time AND stop time    OR    stop time  lt  start time AND NOW    lt  start time AND NOW    lt  stop time    OR    stop time  lt  start time AND NOW    gt  start time    Results  Given    id  1  start time  10 00 00  stop time  15 00 00   id  2  start time  22 00 00  stop time  12 00 00    Selected rows with NOW   09 00 00  2 Selected rows with NOW   14 00 00  1 Selected rows with NOW   11 00 00  1 2 Selected rows with NOW   20 00 00  nothing

User · Answer

This should also work  even in SQL-Server 2005    SELECT   FROM dbo MyTable WHERE Created  gt   DATEADD hh 23 DATEADD day  DATEDIFF day  0  Created - 1   0     AND Created  lt   DATEADD hh 7 DATEADD day  DATEDIFF day  0  Created   0     DEMO

User · Answer

Let us consider a table which stores the shift details     Please check the SQL queries to generate table and finding the schedule based on an input time   Declaring the Table variable  declare  MyShiftTable table MyShift int StartTime time EndTime time    Adding values to Table variable  insert into  MyShiftTable select 1  01 17 40 3530000   02 17 40 3530000  insert into  MyShiftTable select 2  09 17 40 3530000   03 17 40 3530000  insert into  MyShiftTable select 3  10 17 40 3530000   18 17 40 3530000    Creating another table variable with an additional field named  Flag   declare  Temp table MyShift int StartTime time EndTime time Flag int    Adding values to temporary table with swapping the start and end time   insert into  Temp select MyShift case when  StartTime EndTime  then EndTime else StartTime end case when  StartTime EndTime  then StartTime else EndTime  end case when  StartTime EndTime  then 1 else 0  end from  MyShiftTable  Creating input variable to find the Shift  declare  time time convert time  10 12 40 3530000     Query to find the shift corresponding to the time supplied  select myShift from  Temp where   time between StartTime and EndTime and     Flag 0  or   time not between StartTime and EndTime and Flag 1

User · Answer

select   from dbMaster oMaster  where   CAST GETDATE   as time   between   CAST oMaster DateFrom as time    and    CAST oMaster DateTo as time      Please check this

User · Answer

Should be AND instead of OR  select   from MyTable where CAST Created as time   gt    23 00 00      AND CAST Created as time   lt   07 00 00

[sql] Check if a time is between two times (time DataType)

Examples related to sql

Examples related to tsql