1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
UltraCommit
  • 2,236
  • 7
  • 43
  • 61

3 Answers3

2

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);
Community
  • 1
  • 1
praveen
  • 12,083
  • 1
  • 41
  • 49
  • 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. – UltraCommit Jul 24 '12 at 10:21
  • Is it not sufficient to write: SELECT DATEADD(wk, DATEDIFF(wk,0,(GETDATE())-1), 0) ?? I have subtracted 1 to GETDATE, so it works in Italy, using SUNDAY as first day of the week, I have realized only a shift. – UltraCommit Jul 24 '12 at 10:39
  • 1
    Yeah that will surely gonna work but i have edited the code so that you should be aware of the problem .In case if the 1st day of the week changes to some other day ,you should not end up adding or subtracting values from the above function – praveen Jul 24 '12 at 10:41
  • Thank you very much for your kind contribution! – UltraCommit Jul 24 '12 at 11:46
2

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

Omesh
  • 27,801
  • 6
  • 42
  • 51
2

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
Chris Gessler
  • 22,727
  • 7
  • 57
  • 83
  • 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. – UltraCommit Jul 24 '12 at 10:22