Specific Time Range Query in SQL Server

Question

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

• i.e. 3/1/2009 - 3/31/2009
• between 6AM-10PM 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. 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?

I've seen this, but I don't think it's nearly specific enough for this range.

Thanks!

Answer 1

I'm assuming you want all three of those as part of the selection criteria. You'll need a few statements in your where but they will be similar to the link your question contained.

SELECT *   FROM MyTable   WHERE [dateColumn] > '3/1/2009' AND [dateColumn] <= DATEADD(day,1,'3/31/2009')          --make it inclusive for a datetime type     AND DATEPART(hh,[dateColumn]) >= 6 AND DATEPART(hh,[dateColumn]) <= 22          -- gets the hour of the day from the datetime     AND DATEPART(dw,[dateColumn]) >= 3 AND DATEPART(dw,[dateColumn]) <= 5          -- gets the day of the week from the datetime 

Hope this helps.

Answer 2

you can try this (I don't have sql server here today so I can't verify syntax, sorry)

select attributeName   from tableName  where CONVERT(varchar,attributeName,101) BETWEEN '03/01/2009' AND '03/31/2009'    and CONVERT(varchar, attributeName,108) BETWEEN '06:00:00' AND '22:00:00'    and DATEPART(day,attributeName) BETWEEN 2 AND 4