I am trying to get the date range between today's midnight to yesterday's midnight. I just followed this link but it complains about date_trunc
not supporting built in function.
I also tried this way but seems not correct.
where [startTime] <= Convert(DateTime, DATEDIFF(DAY, 0, GETDATE()))
AND [startTime] >= Convert(DateTime, DATEDIFF(DAY, 0, GETDATE()-1))
I think what you are asking for is a way to get yesterdays data when running a report early in the morning. (Your references to midnight are a bit confusing).
This is a very common problem, easily solved by first converting values you are comparing against to dates, and the correctly using >=
(greater than and equals) and <
(Less than).
I've used a variable for the datetime @Now1
to allow it to be changed for testing. But in a real query you can substitute getdate()
.
declare @Now datetime2(0) = '2021-07-16 01:00:00.000';
-- Lets see what the values are
select @Now, cast(@Now as date), dateadd(day, -1, cast(@Now as date));
-- Lets use them in a query
select *
from #Test
-- Where the CreatedDate is "greater than or equal to" the start of yesterday
-- Casting @Now to a date gives the start of today, therefore -1 days gives the start of yesterday
where CreatedDate >= dateadd(day, -1, cast(@Now as date));
-- And CreatedDate is "less than" the start of today
and CreatedDate < cast(@Now as date)
As an aside I would never use GETDATE()-1
because its not obvious what the 1
represents. Better to stick to the DATEADD()
function and be sure.