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()),

Solution 1:

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

SetDATEFIRST7

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

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

Solution 2:

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

Solution 3:

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