March 11, 2009 at 9:38 am
Right, this is prooving to be a right headache, as many date related things in SQL seem to be for some reason:
The 'last week' right now is:
Monday 2nd March
Friday 6th March
So, based on todays date, or indeed ANY date this week I need to write something which basically equates to:
AND communication_date >= Monday 2nd March
AND communication_date <= Friday 2nd March
This would be the same for ANY day this week
I've been trying for quite some time and had a good look around and I can't seem to find anything which would fit the bill.
March 11, 2009 at 10:07 am
select
*
from
MyTable
where
-- Date greater than or equal to Monday of last week
MyDate >= dateadd(dd,((datediff(dd,'17530101',getdate())/7)*7)-7,'17530101')
and
-- Date before Saturday of last week
Mydate < dateadd(dd,((datediff(dd,'17530101',getdate())/7)*7)-2,'17530101')
March 11, 2009 at 10:17 am
Wow, that's mental - never seen the whole '17530101' earliest date type thing before.
Very useful - just wish I had asked before I spent quite a chunk of the afternoon trying to solve it!
thanks very much!
March 11, 2009 at 11:47 am
Michael any reason why you like 17530101 as opposed to 0? I'd never really thought to use the 17530101 like you are doing, but I picked up the use of 0 for doing the same thing from Gregory Larsen's article on databasejournal.com...
peitech you may want to check it out as it includes a number of examples for calculating just about any date you need and has the added benefit of being wicked fast...
-Luke.
March 11, 2009 at 11:56 am
Check out the following code and see if it does what you need:
select getdate(), dateadd(wk, datediff(wk, 0, getdate()) - 1, 0), dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 4
March 11, 2009 at 12:17 pm
Luke L (3/11/2009)
Michael any reason why you like 17530101 as opposed to 0? I'd never really thought to use the 17530101 like you are doing, but I picked up the use of 0 for doing the same thing from Gregory Larsen's article on databasejournal.com...peitech you may want to check it out as it includes a number of examples for calculating just about any date you need and has the added benefit of being wicked fast...
-Luke.
I used 17530101 because the code I posted for the first day of the week would not return the correct start of week date for dates before 0 (19000101). Since there is no SQL Server datetime before 17530101, that isn't a problem.
The code from Gregory Larsen's article has a problem with dates before 19000101. When I post code solutions online, I try to give the most general solution possible, as opposed to code that only works for specific ranges of data. Notice how the code from the Gregory Larsen article fails for 18991231.
select
[MVJ First Day of Week] =
dateadd(dd,((datediff(dd,'17530101',a.DT)/7)*7),'17530101'),
[GL First Day of Week] =
dateadd(wk,datediff(wk,0,DT),0)
from
(
select DT = convert(datetime,'18991231')
) a
Results:
MVJ First Day of Week GL First Day of Week
----------------------- -----------------------
1899-12-25 00:00:00.000 1900-01-01 00:00:00.000
Plenty of other info for working with SQL Server datetime on this link:
Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762
Code for start of week on this link:
Start of Week Function:
March 11, 2009 at 12:51 pm
Thanks for the excellent reply and the links, I'll have to dig into them a bit later this week.
Never really thought about using the dateadd/datediff functionality to get to dates prior to 1900 as I've always been asked to support last week, last quarter type of information. Good to know to look out for it on older dates though.
-Luke.
March 12, 2009 at 3:10 am
Thanks for the indepth replies people - very useful - I tend to do quite a lot with dates so the more knowledge I have on the subject the better.
March 12, 2009 at 1:22 pm
[font="Verdana"]I recommend using a Calendar table. You can encode the week start and week end in there. That's particularly good if your week starts on a "different from normal" day. Does your week start on a Sunday or a Monday?[/font]
September 1, 2009 at 12:52 am
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
September 3, 2009 at 12:22 pm
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.
January 14, 2013 at 11:01 am
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
March 16, 2016 at 11:34 am
Thank you.
This was very helpful:-)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply