CODEWITHSUNDEEP
sql-server
marshpipes
marshpipes

Reputation: 134

Specific Time Range Query With Hours and Minutes in SQL Server

I'm trying to query a specific range of time:

i.e. 3/1/2014 - 09/31/2014 between 15:30 - 18:30 each day Tues/Wed/Thurs only

I've seen that you can get data for a particular range, but only for start to end and this is quite a bit more specific. DATEPART only allows for one time element and I didn't see any SQL Server commands that would directly help me on this, so does anybody else have any thoughts on how you would form this?

Thanks!

SELECT * 
FROM [Order]
WHERE CustomerId = [Customer].Id
    AND BusinessDate BETWEEN '2014-03-01' AND '2014-09-31'
    AND DATEPART(HOUR, FirstSendTime) >= 15
    AND DATEPART(HOUR, FirstSendTime) <= 18

Upvotes: 1

Views: 9523

Answers (3)

Jaugar Chang
Jaugar Chang

Reputation: 3196

Several ways of range 15:30 - 18:30:

DATEPART(HOUR, FirstSendTime)*60+DATEPART(MINUTE, FirstSendTime) between 15*60+30 and 18*60+30

LEFT(CONVERT(TIME, FirstSendTime, 114), 5) between '15:30' and '18:30'

CONVERT(CHAR(5), FirstSendTime, 114) between '15:30' and '18:30'

Several ways of range Tues/Wed/Thurs:

DATEPART(WEEKDAY, FirstSendTime) BETWEEN 3 AND 5

DATENAME(WEEKDAY, FirstSendTime) in ('Tuesday','Wednesday','Thursday')

Upvotes: 0

Dan Guzman
Dan Guzman

Reputation: 46203

Below is one method, assuming that you have a Customer table that wasn't included in your sample query. This might perform better than applying functions to the datetime column of the Orders table.

WITH 
      t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    , t1k AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS num FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d CROSS JOIN t4 AS e)
    , time_ranges AS (
        SELECT 
              DATEADD(minute, (15*60)+30, DATEADD(day, num, '2014-03-01')) AS start_time
            , DATEADD(minute, (18*60)+30, DATEADD(day, num, '2014-03-01')) AS end_time
        FROM t1k
        WHERE 
            num <= DATEDIFF(day, '2014-03-01', '2014-09-30')
            AND DATENAME(weekday, DATEADD(day, num, '2014-03-01')) IN ('Tuesday', 'Wednesday', 'Thursday')
    )
SELECT * 
FROM dbo.[Order]
JOIN dbo.Customer ON Customer.Id = [Order].CustomerId
CROSS JOIN time_ranges
WHERE
    [Order].BusinessDate BETWEEN start_time AND end_time;

Upvotes: 0

TeamTam
TeamTam

Reputation: 1608

SELECT * FROM [Order]
WHERE CustomerId = [Customer].Id
AND BusinessDate BETWEEN '2014-03-01' AND '2014-09-31'
AND (
    DATEPART(HOUR, FirstSendTime) IN (16, 17)
    OR (DATEPART(HOUR, FirstSendTime) = 15 AND DATEPART(MINUTE, FirstSendTime) >= 30)
    OR (DATEPART(HOUR, FirstSendTime) = 18 AND DATEPART(MINUTE, FirstSendTime) <= 30)
)
AND DATEPART(WEEKDAY, FirstSendTime) BETWEEN 3 AND 5

Upvotes: 1

Related Questions