Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Previous week date range Expand / Collapse
Posted Thursday, September 3, 2009 12:22 PM



Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 2,659, Visits: 6,062
ganesaselvam_thylak (9/1/2009)
hi Lynn,
Thanks for your query, can u explain the query?

select getdate(), dateadd(wk, datediff(wk, 0, getdate()) - 1, 0), dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 4

Break that query down into parts and it becomes easy to understand what is goign on. Also if you read the Larson article I referenced a efw posts up it becomes quite clear as well.

Select Getdate() we know what this does, no? IF not it's in BOL

sELECT dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) find the 1st day of last week depending on what your system determines as the 1st day of the week.

The third column just adds 4 days to the previous column.

Again have a look at the article and I think it will become quite clear. Greg explains it in much better detail than I have here. Also see Michael Valentine Jones' replies on using the earliest SQL Server date instead of 0 as Larsen does... It's a good idea if you'll ever need to deal with dates ion those ranges.

Hope that clears some things up.

To help us help you read this

For better help with performance problems please read this
Post #782470
Posted Monday, January 14, 2013 11:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 11:14 AM
Points: 4, Visits: 56

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

select DATEADD(day, -7 ,DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)) LastMondayStart

select DateAdd(ms,-3,(DATEADD(day, -6 ,DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)))) LastMondayEnd

select DATEADD(day, -3 ,DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)) LastFridayStart

select DateAdd(ms,-3,(DATEADD(day, -2 ,DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)))) LastFridayEnd

Post #1406861
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse