Finding Out If Datetime Is In a Specific Time Interval Using SQL

Question

Hi everybody,

How can I find out if a datetime column in my table is in a specific hour interval (e.g 13:07 - 15:15) and in a specific day (e.g Thursdays) in a SQL select statement?

Thanks,

Answer 1

You can use the BETWEEN keyword. Such as ... WHERE MyDate BETWEEN Date1 and Date2

Answer 2

select * From #t

Where DatePart(hour, [date]) >=10 
and DatePart(hour, [date]) <= 11

And DatePart(minute, [date]) >= 15
And DatePart(minute, [date]) <= 45

and Datename(dw,[date]) = 'Thursday' 

Answer 3

I usually use the following syntax:

DECLARE @FromTime DATETIME
DECLARE @ToTime DATETIME
SET @FromTime = '2010-09-02T13:00:00'
SET @ToTime = '2010-09-02T15:00:00'

SELECT Something
FROM MyTable
WHERE DateField >= @FromTime
    AND DateField < @ToTime

You could use BETWEEN but I usually prefer the above instead as BETWEEN is inclusive e.g. in this example, using BETWEEN would include records where the time is 15:00:00.000

Answer 4

Day of week and start/End hours as arguments

declare @tbl table ( datecol datetime )

declare @startHour int
declare @endHour int
declare @specificDay varchar(10)

set @startHour = 13
set @endHour = 15
set @specificDay = 'Thursday'

insert into @tbl select getdate()
insert into @tbl select dateadd(hh, 1, getdate())
insert into @tbl select dateadd(n, 10, getdate())
insert into @tbl select dateadd(dd, 2, getdate())

select * from @tbl 
where  DATENAME(dw, datecol) = @specificDay
and datepart(hh, datecol) >= 13 
and datepart(hh, datecol) <= 15

EDIT:

declare @tbl table ( datecol datetime )

declare @startHour varchar(10)
declare @endHour varchar(10)
declare @specificDay varchar(10)

set @startHour = '13:05:00'
set @endHour = '15:30:00'
set @specificDay = 'Thursday'

insert into @tbl select getdate()
insert into @tbl select dateadd(hh, 1, getdate())
insert into @tbl select dateadd(n, 10, getdate())
insert into @tbl select dateadd(dd, 2, getdate())

select * from @tbl 
where  DATENAME(dw, datecol) = @specificDay
and CONVERT(VARCHAR(8), datecol,8) >= @startHour
and CONVERT(VARCHAR(8), datecol,8) <= @endHour

If time has hours and minutes parts only like '13:05' then change it like this

and CONVERT(VARCHAR(5), datecol,8) >= @startHour
and CONVERT(VARCHAR(5), datecol,8) <= @endHour