Derive the Previous Day in SQL Server

Question

question

BobbyP-1695 asked · Aug 17, 2020 at 08:42 PM

Derive the Previous Day in SQL Server

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.

sql-server-transact-sql

· 1

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft · Aug 24, 2020 at 08:39 AM

Hi @BobbyP-1695,

Please remember to accept the answers if they helped. This can help others who encounter similar problems..

Best Regards
Echo

0 ·

Answer 1 · 2020-08-17T21:18:39.297Z

YitzhakKhabinsky-0887 answered · Aug 17, 2020 at 09:18 PM

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 |
 +-------------------------+-------------------------+

· Share

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2020-08-18T05:15:31.637Z

EchoLiu-msft answered · Aug 18, 2020 at 05:15 AM

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

· Share

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 3 · 2020-08-18T05:55:56.22Z

Viorel-1 answered · Aug 18, 2020 at 05:55 AM

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

· Share

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 4 · 2020-08-18T18:41:50.69Z

GuoxiongYuan-7218 answered · Aug 18, 2020 at 06:41 PM

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

· Share

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 5 · 2020-08-18T18:48:25.767Z

TomPhillips-1744 answered · Aug 18, 2020 at 06:48 PM

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/

· Share

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 6 · 2020-08-18T18:50:33Z

GuoxiongYuan-7218 answered · Aug 18, 2020 at 06:50 PM

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

· Share

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 7 · 2020-11-05T22:17:47.207Z

IanBates-4416 answered · Nov 05, 2020 at 10:17 PM

Hi @BobbyP-1695 ,
This should work for you.

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

image.png (5.4 KiB)

· Share

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question