How to get data between 2 times that work across midnight

I need to find out how to get data between 2 times. Note the time crosses midnight

The current SQL i have will not report information after midnight but reports data before.

Example 1 works fine
Select EventID, Keyholder, keyholderID, Datetime from Table
where datetime between '2017-09-19' and '2017-09-21'
and convert(time, datetime) between '22:00:00' AND '23:59:59'

Result set
3571591      Parsons Ivan      391      2017-09-20 22:07:39.000


Example 2 dosen't work

Select EventID, Keyholder, keyholderID, Datetime from test
where eventcategory = 2
and keyholderID = 391
and datetime between '2017-09-19' and '2017-09-21'
and convert(time, datetime) between '22:00:00' AND '05:59:59'

Result set
no Results foun
mlowe71Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
AND ( DATEPART(HOUR, datetime) >= 22 OR DATEPART(HOUR, datetime) < 6 )
0
mlowe71Author Commented:
Thank you for that Scott, is there an easy Way to query on the days of the week as well.

Example 2000 and 0600 Monday to Friday and all day on Saturdays and Sundays ?

Thank you in Advance

Martin
0
Scott PletcherSenior DBACommented:
Yes.  And you want to do it so that it is completely independent of SQL settings, such as @@DATEFIRST / which language (English, French, whatever).

My preference is to do a DATEDIFF from a known day.  The most common date used in SQL Server is day 0, or 1900-01-01.  That date was a Monday.  So, if we calc the number of days from that date, excluding whole weeks, we'll get the week day.

Seems a little confusing without an example.  But, say that calc gives us 2, that tells us two days past Monday, it must be Wednesday.  This calc is easy and always accurate, with doesn't require @@DATEFIRST to appear in the calc.

Here's the actual code:

AND ( DATEDIFF(DAY, 0, datetime) IN (5, 6) /*Sat, Sun, don't need to check hour*/ OR
           /*if NOT Sat/Sun, check hour too*/  (DATEPART(HOUR, datetime) >= 22 OR DATEPART(HOUR, datetime) < 6 ))
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mlowe71Author Commented:
Hi Again

Not sure if I'm missing something but if I run the SQL on my test table as below I get no results found.
Im presuming the query is saying give me all events on Sat, Sun regardless of time

select * from test where  DATEDIFF(DAY, 0, datetime) IN (5, 6)



ID      datetime
1      2018-02-25 15:34:58.160
2      2018-02-26 15:34:58.160
3      2018-02-27 15:34:58.160
4      2018-02-28 15:34:58.160
5      2018-03-01 15:34:58.160
6      2018-03-02 15:34:58.160
7      2018-03-03 15:34:58.160
8      2018-03-04 15:34:58.160
9      2018-03-05 15:34:58.160
10      2018-03-06 15:34:58.160
11      2018-03-07 15:34:58.160
12      2018-03-08 15:34:58.160
13      2018-03-09 15:34:58.160
14      2018-03-10 15:34:58.160
0
Scott PletcherSenior DBACommented:
D'OH, sorry, I forgot to exclude whole weeks in the calc (although I stated it in the description, DOUBLE D'OH):

DATEDIFF(DAY, 0, datetime) % 7 IN (5, 6)
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlowe71Author Commented:
HI Scott

That is looking much better, sorry I was on overload myself Friday..Trying to get back into SQL work myself not used it much since 2000 :-)

Will test the SQL on Monday but from my test system its looking really good....Thank you so much for your time and effort.

speak soon Martin
0
mlowe71Author Commented:
HI Scott

Checked and all ok, Thank you so much for you help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.