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 @Now1to 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.