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
Author
Message
Posted Thursday, September 3, 2009 12:22 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:29 AM
Points: 2,655, Visits: 6,016
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.
-Luke.


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