question

BobbyP-1695 avatar image
0 Votes"
BobbyP-1695 asked ·

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

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

Hi @BobbyP-1695,

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

Best Regards
Echo



0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered ·

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

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

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered ·

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

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

Viorel-1 avatar image
0 Votes"
Viorel-1 answered ·

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

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

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered ·
 SELECT CAST(DATEADD(DAY,-1,GETDATE()) AS date);
· Share
10 |1000 characters needed characters left characters exceeded

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

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered ·

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

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

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered ·

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

· Share
10 |1000 characters needed characters left characters exceeded

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

IanBates-4416 avatar image
0 Votes"
IanBates-4416 answered ·

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

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

37768-image.png



image.png (5.4 KiB)
· Share
10 |1000 characters needed characters left characters exceeded

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