SQL filter between start time of day 0:00 and 22 hours later-CodePudding

I need to create a filter in sql, but I don't know how exactly.

WHERE date < ?? /*I need it to be today until 10pm */ and
date >= getdate() /* I used getdate(), but I don't know if it's the best way, as I need it to get entries from 0am*/

I apologize if I didn't explain myself properly, but I'll try to explain better if I need to.

CodePudding user response：

today until 10pm

SELECT DATETIME2FROMPARTS(YEAR(getdate()), MONTH(getdate()), DAY(getdate()), 10, 0, 0, 0, 0)

entries from 0am

SELECT CAST(GETDATE() AS DATE)

Final :

WHERE MyColumnDateTime BETWEEN CAST(GETDATE() AS DATE) 
AND DATETIME2FROMPARTS(YEAR(getdate()), MONTH(getdate()), DAY(getdate()), 22, 0, 0, 0, 0)

CodePudding user response：

So you want to compare on today at 00:00:00 and today at 22:00:00

First you need to get a Date from the current date with the hour 00:00:00
This can be done like this

select convert(date, getdate())

Next you need to get a datetime from the current date with the hour 22:00:00
This can be done like this

select dateadd(hour, 22, convert(datetime2(0), convert(date, getdate())))

Now you can use these 2 dates in your where clause like this

select *
from   yourTable t
where  t.yourDate > convert(date, getdate())
and    t.yourDate <= dateadd(hour, 22, convert(datetime2(0), convert(date, getdate())))

If today is 13/10/2022 than this will retrieve all rows between 13/10/2022 00:00:00 and 13/10/2022 22:00:00