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!

Do you have date and time in the same column, or separate? – Lasse V. Karlsen May 19 '09 at 21:47 — Lasse V. Karlsen, May 19 '09 at 21:47
Same Column - it is just a DateTime field – Fry May 19 '09 at 21:58 — Fry, May 19 '09 at 21:58

score 35 · Accepted Answer · edited Jul 13 '17 at 13:05

35

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.

edited Jul 13 '17 at 13:05

Giulio Caccin

2,962
6
36
57

answered May 19 '09 at 21:59

Giggy

526
4
8

1

Syntax for dateadd is `DATEADD(datepart,number,date)` Sample should be: `...<= DATEADD(day, 1, '3/31/2009')` Source: [Technet](http://technet.microsoft.com/en-us/library/ms186819(v=sql.90).aspx) – user797717 Aug 31 '14 at 19:15
3

Yeah but how would you do this if the time was not precise to exact hour for example if you want in time range from 6:15 AM to 10:35 AM? – Marko Mar 12 '15 at 21:08

northpole · Answer 2 · 2009-05-20T01:27:20.550

6

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

edited May 20 '09 at 01:27

answered May 19 '09 at 22:12

northpole

10,244
7
35
58

Thanks :) It's very similar to what Giggy suggested. I tried this originally after his suggestion, but decided to go with strict equality signs as they removed the ambiguity of what part of 'between' is inclusive :) – Fry May 19 '09 at 22:21

score 2 · Answer 3 · answered Nov 23 '17 at 07:43

2

I (using PostgrSQL on PGadmin4) queried for results that are after or on 21st Nov 2017 at noon, like this (considering the display format of hours on my database):

select * from Table1 where FIELD >='2017-11-21 12:00:00'

answered Nov 23 '17 at 07:43

Z.storch

31
2

score 0 · Answer 4 · answered May 19 '09 at 21:51

0

select * from table where 
(dtColumn between #3/1/2009# and #3/31/2009#) and 
(hour(dtColumn) between 6 and 22) and 
(weekday(dtColumn, 1) between 2 and 4)

answered May 19 '09 at 21:51

Tolgahan Albayrak

3,118
1
25
28

3

This is valid SQL Server 2005 syntax? It's not according to my SQLServer – Fry May 19 '09 at 21:56
Does this even work in 2008? Seems like useful date functions took until 2012 to become available. – ficuscr Feb 04 '13 at 20:00
I really do not think that is a real T-sql method. – ficuscr Feb 04 '13 at 20:12

score -1 · Answer 5 · edited Feb 15 '22 at 14:58

-1

If you want to get results for specific time period, use the query below:

Select * from [your_table_name] 
where date between '2021-10-05' AND '2021-12-10'

edited Feb 15 '22 at 14:58

benson23

16,369
9
19
38

answered Feb 11 '22 at 19:49

Mandeep Kaur

1
1

Specific Time Range Query in SQL Server

5 Answers5

Linked

Related