November 11, 2010 at 12:59 pm
Craig Farrell (11/11/2010)
WayneS (11/11/2010)
Formula: DATEADD(dd,DATEDIFF(dd,-7,DateOfPurchase)/7*7,-1))select DATENAME(weekday, 0); -- = Monday
select DATENAME(weekday, -1); -- = Sunday
select DateAdd(day, -7, 0); -- = 18991225
So, take the number of days between 18991225 (Date=-7) and the date in the field.
Divide this by the number 7 (number of days in a week). Since this is an integer (7) vs. decimal # (7.0), the result will have the fractions of a week truncated. Multiply back by 7 to get the number of days for entire weeks. Add this to 18991231 (Date=-1, a Sunday) to get the start of the week.
Clear as mud?
Okay, now I'm with John. 🙂
My understanding was this:
Take 7 days off off DateOfPurchase. Basically, move it to the same day, last week.
Next, strip the decimal off (/7*7). this moves it to the Monday of last week.
Now, move it forward a day via the dateadd and datediff from the -1 to the Tuesday of last week.
What's got me confused is... why are we moving backwards a week?
Thanks for the explanation Wayne....good stuff. The part I missed is, I think, the same part that Craig is missing now.
Craig,
The -7 is not taking it back 7 days.....the DATEDIFF function accepts 3 parmaters, 2 of which are datetime data types. The -7 is in one of the datetime places so it gets cast to datetime. This means that it represents 1899-12-25. The /7*7 strips the decimal as Wayne suggests and the -1 as the second arguement for the DATEADD function (also a datetime) gets cast as 1899-12-31. Knowing this is a Sunday makes this work as they just add to it to get the next Sunday.
November 11, 2010 at 1:44 pm
Craig Farrell (11/11/2010)
What's got me confused is... why are we moving backwards a week?
If you take the first week (1899-12-31 to 1900-01-05), all of these will evaluate to week zero. Since weeks are not zero-based, we start back one week so that the we'll get the proper week number. (#days(0-6) / 7 * 7 = 0)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 11, 2010 at 1:45 pm
John Rowan (11/11/2010)
The -7 is not taking it back 7 days.....the DATEDIFF function accepts 3 parmaters, 2 of which are datetime data types. The -7 is in one of the datetime places so it gets cast to datetime. This means that it represents 1899-12-25. The /7*7 strips the decimal as Wayne suggests and the -1 as the second arguement for the DATEADD function (also a datetime) gets cast as 1899-12-31. Knowing this is a Sunday makes this work as they just add to it to get the next Sunday.
Exactly! :w00t::w00t:
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 11, 2010 at 1:50 pm
November 11, 2010 at 2:36 pm
I think I'm confusing myself.
Here's my function checks so we all understand what the heck I'm talking about as I ramble on a bit here:
DECLARE @testDate DATETIME
SET @testDate = GETDATE()
--PRINT '@@DATEFIRST: ' + CAST( @@DATEFIRST AS VARCHAR(10))
PRINT 'What''s the date we''re testing for?'
print '@testdate: ' + CONVERT( VARCHAR(30), @testdate)
PRINT ''
PRINT 'The # of days from 1/1/1900'
PRINT 'DATEDIFF ( dd, 0, @testdate): ' + CAST( DATEDIFF ( dd, 0, @testdate) AS VARCHAR(30))
PRINT ''
PRINT 'The number of days since 12/25/1900'
PRINT 'DATEDIFF ( dd, -7, @testdate): ' + CAST( DATEDIFF ( dd, -7, @testdate) AS VARCHAR(30))
PRINT ''
PRINT 'The number of days since 12/25/1900 to the most recent monday.'
PRINT 'DATEDIFF ( dd, -7, @testdate) /7 *7: ' + CAST( DATEDIFF ( dd, -7, @testdate) /7 *7 AS VARCHAR(30))
PRINT ''
PRINT 'The most recent Monday'
print DATEADD( dd, DATEDIFF ( dd, 0, @testdate) /7 *7, 0)
PRINT ''
PRINT 'The most recent Sunday'
print DATEADD( dd, DATEDIFF ( dd, 0, @testdate) /7 *7, -1)
PRINT ''
PRINT 'With a series of different test dates'
SET @testDate = GETDATE() -1
PRINT @testdate
print DATEADD( dd, DATEDIFF ( dd, -7, @testdate) /7 *7, -1)
SET @testDate = GETDATE() -2
PRINT @testdate
print DATEADD( dd, DATEDIFF ( dd, -7, @testdate) /7 *7, -1)
SET @testDate = GETDATE() -3
PRINT @testdate
print DATEADD( dd, DATEDIFF ( dd, -7, @testdate) /7 *7, -1)
SET @testDate = GETDATE() -4
PRINT @testdate
print DATEADD( dd, DATEDIFF ( dd, -7, @testdate) /7 *7, -1)
SET @testDate = GETDATE() -5
PRINT @testdate
print DATEADD( dd, DATEDIFF ( dd, -7, @testdate) /7 *7, -1)
SET @testDate = GETDATE() -6
PRINT @testdate
print DATEADD( dd, DATEDIFF ( dd, -7, @testdate) /7 *7, -1)
SET @testDate = GETDATE() -7
PRINT @testdate
print DATEADD( dd, DATEDIFF ( dd, -7, @testdate) /7 *7, -1)
PRINT ''
PRINT 'Here is where it breaks down for my understanding'
SET @testdate = GETDATE()
print DATEADD( dd, DATEDIFF ( dd, -7, @testdate) /7 *7, 3)
print DATEADD( dd, DATEDIFF ( dd, -7, @testdate) /7 *7, 2) --Next Wednesday
print DATEADD( dd, DATEDIFF ( dd, -7, @testdate) /7 *7, 1) --Next Tue
print DATEADD( dd, DATEDIFF ( dd, -7, @testdate) /7 *7, 0) -- Next Mon
print DATEADD( dd, DATEDIFF ( dd, -7, @testdate) /7 *7, -1) -- Next Sun
print DATEADD( dd, DATEDIFF ( dd, -7, @testdate) /7 *7, -2) -- Next Sat
print DATEADD( dd, DATEDIFF ( dd, -7, @testdate) /7 *7, -3) -- Next Fri
print DATEADD( dd, DATEDIFF ( dd, -7, @testdate) /7 *7, -4) -- Today, Thur.
print DATEADD( dd, DATEDIFF ( dd, -7, @testdate) /7 *7, -5)
print DATEADD( dd, DATEDIFF ( dd, -7, @testdate) /7 *7, -6)
As you can see I'm a little lost in deciphering the functionality at the tail of this, primarily with the linearity expected of the function. This may just be I've got the offset in the wrong location, which I think I do. For example, this function finds the eow date, when it's Sunday.
If I wanted to end on Wednesdays however, I wouldn't continue backwards to Wednesday, I'd want to use +2 instead of -5, even though -5 is in the week we 'backed into', and +2 is in the 'next week'.
What am I missing here?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 11, 2010 at 6:45 pm
Perhaps the following code will make it easier to explain what was going through my head when I wrote the forumula. A couple of big hints here is the 1) /7*7 will always round DOWN because of the Integer math, 2) the -7 isn't a minus 7... it's a -1 plus a -6. Everything else is just like stripping the time except I used a known Sunday (-1) instead of 0.
A math hint is that if you go more negative, you're getting further away from positive numbers... effectively, you're adding days to the span of days between the negative number and the positive number. Adding -6 increased the span of days by 6. The reason why I didn't use 7 is because if the original date was a Sunday to begin with, I didn't want it to become NEXT Sunday. Instead, I wanted 6/7 to occur so it would still round down to that original Sunday.
Here's the code with a couple of comments. Each column shows the further progression of the formula until it did what I wanted it to do. 😛
WITH
cteDates AS
(
SELECT OriginalDate = DATEADD(dd,t.N-1,'Aug 2010')
FROM dbo.Tally t
WHERE t.N BETWEEN 1 AND 365
)
SELECT OriginalDate,
OriginalDoW = DATENAME(dw,OriginalDate),
Days = DATEDIFF(dd,-1,OriginalDate), --Days since a known Sunday
WeekRoundedDown = DATEDIFF(dd,-1,OriginalDate)/7*7, --Convert days to whole weeks ending on Sunday (Rounds DOWN from Integer math) WeekDateRoundedDown = DATEADD(dd,DATEDIFF(dd,-1,OriginalDate)/7*7,-1), --Rounds correctly but is a week too low
DaysToCorrectSunday = DATEDIFF(dd,-7,OriginalDate)/7*7, --Add 6 days back in before rounding by /7*7. We only use 6 because we don't want Sunday to change (Distance increases from postive numbers by 6)
CorrectSundayEoW = DATEADD(dd,DATEDIFF(dd,-7,OriginalDate)/7*7,-1) --Add the days back to the known Sunday and we're done
FROM cteDates
;
[font="Arial Black"]Don't think dates here. Instead, think whole weeks and how partial weeks will always be rounded down to a whole week.[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
November 11, 2010 at 9:28 pm
Well done Jeff. I turn the podium back over to the master.:-D
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 12, 2010 at 8:05 am
WayneS (11/11/2010)
Well done Jeff. I turn the podium back over to the master.:-D
Shucks :blush:... you folks are doing just fine and it's a pleasure to be in your good company. The only reason I jumped back in was to really explain that the -7 wasn't what people thought and that understanding what it actually is makes this whole thing a whole lot more simple.
It does bring up a good point, though. Combining -1 and -6 obfuscated what was happening in the formula. I need to document that better in the future.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2010 at 8:06 am
Hmmmm... you know... we run across this type of thing often enough... perhaps an "SQL Spackle" article is in order.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2010 at 9:25 am
Just a little nitpicking on this; Jeff's code does not work on dates before 1899-12-25.
I posted a function on the link below that works over the entire range of possible datetime values. If you look at the code of this function, you will see that it is very similar to what Jeff posted, with additional logic to handle the "edge" cases and to work for any start day of the week
End of Week Function available on this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64760
select
OriginalDate,
JeffSundayEoW =
DATEADD(dd,DATEDIFF(dd,-7,OriginalDate)/7*7,-1),
MVJSundayEoW =
dbo.F_END_OF_WEEK(OriginalDate,2)
from
( select OriginalDate = convert(datetime,'18000101') ) a
order by
OriginalDate
Results:
OriginalDate JeffSundayEoW MVJSundayEoW
----------------------- ----------------------- -----------------------
1800-01-01 00:00:00.000 1800-01-12 00:00:00.000 1800-01-05 00:00:00.000
Edit:
This code shows an inline code solution for any ending day of week, Sunday through Saturday. It will work for any date within the range of 1753-01-08 through 9999-12-24. If you are wondering, the numbers -53690 through -53684 through represent dates 1753-01-01 through 1753-01-07.
select
a.DT,
EOWSun = dateadd(dd,((datediff(dd,-53690,a.DT)/7)*7)+6,-53690),
EOWMon = dateadd(dd,((datediff(dd,-53689,a.DT)/7)*7)+6,-53689),
EOWTue = dateadd(dd,((datediff(dd,-53688,a.DT)/7)*7)+6,-53688),
EOWWed = dateadd(dd,((datediff(dd,-53687,a.DT)/7)*7)+6,-53687),
EOWThu = dateadd(dd,((datediff(dd,-53686,a.DT)/7)*7)+6,-53686),
EOWFri = dateadd(dd,((datediff(dd,-53685,a.DT)/7)*7)+6,-53685),
EOWSat = dateadd(dd,((datediff(dd,-53684,a.DT)/7)*7)+6,-53684)
from
( -- Generate test dates for 2010-01-01 through 2011-01-01
select
DT = dateadd(dd,aa.number,'20100101')
from
dbo.F_TABLE_NUMBER_RANGE(0,365) aa
) a
order by
a.DT
Alternative inline code:
select
a.DT,
EOWSun = dateadd(dd,((datediff(dd,'17530101',a.DT)/7)*7)+6,'17530101'),
EOWMon = dateadd(dd,((datediff(dd,'17530102',a.DT)/7)*7)+6,'17530102'),
EOWTue = dateadd(dd,((datediff(dd,'17530103',a.DT)/7)*7)+6,'17530103'),
EOWWed = dateadd(dd,((datediff(dd,'17530104',a.DT)/7)*7)+6,'17530104'),
EOWThu = dateadd(dd,((datediff(dd,'17530105',a.DT)/7)*7)+6,'17530105'),
EOWFri = dateadd(dd,((datediff(dd,'17530106',a.DT)/7)*7)+6,'17530106'),
EOWSat = dateadd(dd,((datediff(dd,'17530107',a.DT)/7)*7)+6,'17530107')
from
( --Generate test dates for 2010-01-01 through 2011-01-01
select
DT = dateadd(dd,aa.number,'20100101')
from
dbo.F_TABLE_NUMBER_RANGE(0,365) aa
) a
order by
a.DT
Number Table Function F_TABLE_NUMBER_RANGE available here
November 12, 2010 at 2:47 pm
Jeff Moden (11/11/2010)
Perhaps the following code will make it easier to explain what was going through my head when I wrote the forumula. A couple of big hints here is the 1) /7*7 will always round DOWN because of the Integer math, 2) the -7 isn't a minus 7... it's a -1 plus a -6. Everything else is just like stripping the time except I used a known Sunday (-1) instead of 0.
Took me five times reading this and a bunch more caffeine than I expected... but I think I finally got the V8 head thump. It came along with a D'oh! The explaination of the -1 and -6 helped a lot to undo my confusion. I still don't entirely get it but you got me over the hurdle, I'll puzzle the rest out. :w00t:
I had to end up breaking your code down to each of the days in question to finish making sense of it.
Thanks again!
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 12, 2010 at 3:49 pm
Craig Farrell (11/12/2010)
Jeff Moden (11/11/2010)
Perhaps the following code will make it easier to explain what was going through my head when I wrote the forumula. A couple of big hints here is the 1) /7*7 will always round DOWN because of the Integer math, 2) the -7 isn't a minus 7... it's a -1 plus a -6. Everything else is just like stripping the time except I used a known Sunday (-1) instead of 0.Took me five times reading this and a bunch more caffeine than I expected... but I think I finally got the V8 head thump. It came along with a D'oh! The explaination of the -1 and -6 helped a lot to undo my confusion. I still don't entirely get it but you got me over the hurdle, I'll puzzle the rest out. :w00t:
I had to end up breaking your code down to each of the days in question to finish making sense of it.
Thanks again!
You might find it easier to understand it this way to overcome the confusion of -7 -1 and to just see it done with dates:
First, find the difference in days between 1753-01-01 (a Monday) and OriginalDate.
Then divide that by 7 and multiply by 7 to get the number of days that should be added to 1753-01-01 to find the beginning of the week (Monday).
Then add 6 to that to get the following Sunday.
And finally add that to 1753-01-01.
select dateadd(dd,((datediff(dd,'17530101',OriginalDate)/7)*7)+6,'17530101')
from mytable
Note that any Monday date, not just 1753-01-01, can be used as long as it is earlier than OriginalDate. I chose 1753-01-01 because it is the earliest possible datetime that is a Monday, so there is no possibility of OriginalDate being earlier.
Jeff built the addition of +6 into the code (to find the following Sunday) by his selection of dates: -7 (1899-12-25) and -1 (1899-12-31). The code below shows it done this way.
select dateadd(dd,((datediff(dd,'17530101',OriginalDate)/7)*7),'17530107')
from mytable
November 12, 2010 at 5:44 pm
Michael Valentine Jones (11/12/2010)
You might find it easier to understand it this way to overcome the confusion of -7 -1 and to just see it done with dates:First, find the difference in days between 1753-01-01 (a Monday) and OriginalDate.
Then divide that by 7 and multiply by 7 to get the number of days that should be added to 1753-01-01 to find the beginning of the week (Monday).
Then add 6 to that to get the following Sunday.
And finally add that to 1753-01-01.
Just needed one more paraphrasing. That did it. Thank you Michael. I was confusing myself, the above and this formula was the untangler for me. 😎
select dateadd(dd,((datediff(dd,'17530101',OriginalDate)/7)*7)+6,'17530101')from mytable
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 12, 2010 at 6:28 pm
Michael Valentine Jones (11/12/2010)
Note that any Monday date, not just 1753-01-01, can be used as long as it is earlier than OriginalDate. I chose 1753-01-01 because it is the earliest possible datetime that is a Monday, so there is no possibility of OriginalDate being earlier.
:ermm: Guess we need a new routine to handle SQL 2008's new DATE and DATETIME2 formats. '0001-01-01' is now the earliest known Monday.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 12, 2010 at 7:13 pm
WayneS (11/12/2010)
Michael Valentine Jones (11/12/2010)
Note that any Monday date, not just 1753-01-01, can be used as long as it is earlier than OriginalDate. I chose 1753-01-01 because it is the earliest possible datetime that is a Monday, so there is no possibility of OriginalDate being earlier.:ermm: Guess we need a new routine to handle SQL 2008's new DATE and DATETIME2 formats. '0001-01-01' is now the earliest known Monday.
There are other issues to deal with. One reason that 1753 is the start of datatime is because the switch to the Gregorian calendar happpened in the English speaking world in 1752, so 1753 is the first full year on the Gregorian calendar.
Britain and the British Empire adopted the Gregorian calendar in 1752 when Wednesday, 2 September 1752 was followed by Thursday, 14 September 1752. In Alaska, the change took place when Friday, 6 October 1867 was followed again by Friday, 18 October after the US purchase of Alaska from Russia, which was still on the Julian calendar. Other countries adopted the Gregorian calendar at other times.
It leaves the question of exactly how to handle those earlier dates if you are dealing with historical dates. Or, you can just ignore the whole issue and assume we have been on the same calendar since 0001-01-01.
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply