Finding Out If Datetime Is In a Specific Time Interval Using SQL
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,
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
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'
You can use the BETWEEN keyword. Such as ... WHERE MyDate BETWEEN Date1 and Date2
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
精彩评论