CODEWITHSUNDEEP
sql-server
UltraCommit
UltraCommit

Reputation: 2276

SQL Server 2005: arithmetic with dates

I would like to write a simple SELECT statement in SQL Server 2005 which does the following computation with date arithmetic:

Starting from the present date (this means getdate()), determine the previous Monday, and then subtract 70 days from that Monday, showing in output the resulting date.

How could I achieve this?

My difficulty is mainly to determine the previous Monday.

Of course, if getdate() is Monday, the previous Monday is getdate()

Thank you in advance for your kind help.

UltraCommit

EDIT: Please note that in Italy the first day of the week is Monday and not Sunday, so if the input is Sunday, July 29th, 2012, the output has to be 23rd July, and not 30th July.

Upvotes: 1

Views: 392

Answers (3)

praveen
praveen

Reputation: 12271

This will retrieve monday for the current week 

Select DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)

and then you need to subtract 70 from the above day

SELECT Dateadd(DAY,-70,DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0))

Edit : Please go through the answer posted in SO

Monday is displayed as Current Week because DATEFIRST which indicates the 1st day of the week is set to monday .In order to set it to Sunday ,you need to change the setting to Sunday 

 Set DATEFIRST 7

Else as suggested in the above SO link ,you need to change your code 

 DECLARE @dt DATE = '1905-01-01';
 SELECT [start_of_week] = DATEADD(WEEK, DATEDIFF(WEEK, @dt, CURRENT_TIMESTAMP), @dt);

Upvotes: 2

Chris Gessler
Chris Gessler

Reputation: 23113

This should get you started. It will find the past Monday for the current week.

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) MondayOfCurrentWeek

To substract 70 days, just add -70 to the end:

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)-70 as SomeMondayInHistory

Upvotes: 2

Omesh
Omesh

Reputation: 29091

To get last monday look at http://blog.sqlauthority.com/2007/08/20/sql-server-find-monday-of-the-current-week/

Upvotes: 2

Related Questions