Using DATEDIFF or DATEPART for records with specific TIME(Hours)

Transact-SQL
1

Hey all!
I can't seem to figure out how to pull results from a table between 2 specific times. I need to be able to get results specifically between 5PM(17) and 7AM, not the other way around...I can't use DAYS because obviously after 23:59 the day will change, and if i only use Hour, i think the same issue applies. I can't seem to wrap my head around the login here.
Any help is appreciated :smile:

The table in question are just records of CompanyIDs, and i'm using the createdate value in this case to only return companies that were created outside of our office hours. Result set would be something like

Cmp_ID, cmp_name, cmp_createdate,cmp_createdby

EDIT: If i run this as 2 seperate queries, first for entries with an hour >=17 and 1 where Hour <7, that gives me results for all entries, but i will need this to be a recurring report so i have to find a way to have to send 1 result set

2

In your query you can combine the two - for example,

WHERE
	( DATEPART(HOUR, cmp_createdDate) >= 17 OR DATEPART(HOUR, cmp_createdDate) < 7 )
	AND --- your other where conditions here.

If that does not solve the issue, can you post your query and some sample data?

3

Thanks for the tip, that did return a good amount of results, but out of curiosity, i tried typing the WHERE clause as just

DATEPART(HOUR,cmp_createdate in (1,2,3,4,5,6,18,19,20,21,22,23,24)

The first query returned 14188 results and the second 1 only returned 213...Any idea how or why these differ? Shouldn't the values in in my second query be equivalent to the 1 above?

4

The equivalent of what you posted would be a strictly > 17 rather than >= 17 as I had posted earlier.

WHERE
	( DATEPART(HOUR, cmp_createdDate) > 17 OR DATEPART(HOUR, cmp_createdDate) < 7 )
	AND --- your other where conditions here.