0

If found this question which really exactly like my need the only problem its my range of time is 21:00 to 5:00 am!

Specific Time Range Query in SQL Server

I need a query for the whole between 21:00 to 05:00

but i made a query the problem is my query return 21:00 to 23:59 for the month

Select BeginServiceTime,FinishServiceTime From table where
(BeginServiceTime between '2015-07-01' and '2015-07-31') and          
  DATEPART(hh,BeginServiceTime) >= 21

And i need to make another one for the 00:01 to 05:00 like :

Select BeginServiceTime,FinishServiceTime From table where
(BeginServiceTime between '2015-07-01' and '2015-07-31') and          
  DATEPART(hh,BeginServiceTime) =< 5

the question : how can i make both in 1 query?

Community
  • 1
  • 1
Tidoy007
  • 79
  • 7

1 Answers1

0

The values between 21:00 and 05:00 are logically the opposite of the values between 05:00 and 21:00 so you could just invert the condition to filter out times between 05:00 and 21:00:

where (BeginServiceTime between '2015-07-01' and '2015-07-31') 
  and not (DATEPART(hh,BeginServiceTime) < 21 and DATEPART(hh,BeginServiceTime) > 5)

If you want more control over the times you filter by you could cast the datetime to a time value and use this:

and not (
  cast(BeginServiceTime as time) > '05:00:00' and cast(BeginServiceTime as time) < '21:00:00'
)
jpw
  • 44,361
  • 6
  • 66
  • 86
  • 1
    lol man i'm slapping me in the face right now ,i need to try this!!!Never think of invert the condition! – Tidoy007 Aug 26 '15 at 21:30