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

Hi BobbyP,

If it is T-SQL statement, why not to use the FORMAT() function?

DECLARE @var DATETIME = GETDATE();

SELECT @var AS [Before]
 , FORMAT(DATEADD(DAY,-1,@var),'yyyy-MM-dd 00:00:00.000') AS [After];

Output

+-------------------------+-------------------------+
|         Before          |          After          |
+-------------------------+-------------------------+
| 2020-08-17 17:26:01.953 | 2020-08-16 00:00:00.000 |
+-------------------------+-------------------------+

Answer 2

Hi BobbyP,
Using format function is indeed a good choice.Use the FORMAT function for locale-aware formatting of date/time and number values as strings,
more details please refer to ：FORMAT (Transact-SQL)

Best Regards
Echo

Answer 3

If you want to remove the time part, then consider a conversion like this: ‘cast(some_datetime as date)’.

Answer 4

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

Answer 5

You would be much better off using a calendar table and then you can just use "First Day of Week" and "Last Day of Week" values and never need to calculate the values.

https://weblogs.sqlteam.com/dang/2010/07/19/calendar-table-and-datetime-functions/