Derive the Previous Day in SQL Server

Question

We have traditionally derived our date parameters in a SSIS Package with the following syntax...

SELECT DATEADD(DAY,-1,(DATEADD(WEEK,(DATEPART(WK,'2020-08-19 05:00:00.000')),DATEADD(YEAR,DATEPART(YEAR,'2020-08-19 05:00:00.000')-1900, 0)) - 4 -
DATEPART(DW,DATEADD(WEEK,(DATEPART(WK,'2020-08-19 05:00:00.000')),DATEADD(YEAR,DATEPART(YEAR,'2020-08-19 05:00:00.000')-1900, 0)) - 4))) AS [EndDate_In]
;

Typically we would use GETDATE() rather than the date indicated...'2020-08-19 05:00:00.000'...But I am trying in vain to try and return '2020-08-18 00:00:00.000' from this mess and I cannot seem to get it.

Can anyone please help me?

Like I said, we want to be consistent the way we derive our @StartDAte_In and @EndDate_In Parameters so we have to stick to this kind of format. I just cannot seem to get the math correct.

Thanks for your review and am hopeful for a reply.

Answer 1

Try this:
SELECT CAST(DATEADD(DAY,-1,GETDATE()) AS date);

Answer 2

Hi @Bobby P ,
This should work for you.

SELECT DATEADD(D,-1,DATEDIFF(D,0,GETDATE())) AS YesterdayWithZeroTime  

Answer 3

SELECT GetDate()
2021-04-18 21:18:14.407

SELECT Convert(DATE, GetDate())
2021-04-18

SELECT DateAdd(dd,-1,Convert(DATE, GetDate()))
2021-04-17