CODEWITHSUNDEEP
sql-serverdatetimebetween
James Steele
James Steele

Reputation: 654

SQL Server count between before and after midnight - 24 hours

I need to count records between before midnight and after midnight with a field that uses 24 hour datetime. The following statement has worked perfectly for times that are in the same day, e.g. between 10:30 am and 4:29 pm:

WHERE CAST (theader_tdatetime AS time) BETWEEN '10:30' and '16:29'

However, I need to also select records from 7:31 pm to 2:00 am, and my statement no longer works:

WHERE CAST (theader_tdatetime AS time) BETWEEN '19:31' and '02:00'

How can I select records from this timeframe?

Edit: A few sample records that bridge the midnight gap

2015-11-20 23:48:52.000  
2015-11-20 23:49:58.000  
2015-11-20 23:51:35.000  
2015-11-20 23:58:51.000  
2015-11-20 23:59:26.000  
2015-11-21 00:04:03.000  
2015-11-21 00:04:36.000  
2015-11-21 00:05:11.000

Upvotes: 0

Views: 1239

Answers (2)

Cato
Cato

Reputation: 3701

WHERE CAST (theader_tdatetime AS time) >= '19:31' OR CAST (theader_tdatetime AS time) <= '02:00'

Upvotes: 0

Michael Rudner Evanchik
Michael Rudner Evanchik

Reputation: 877

WHERE CAST (transaction_date AS time)
BETWEEN '15:30 PM' and '23:59 PM' 
or CAST (transaction_date AS time) BETWEEN '0:00 AM' and '02:00 AM'

Upvotes: 2

Related Questions