December 4, 2012 at 5:48 pm
Jeff Moden (12/4/2012)
Then substitute a 0 for Michael's -53690. 😀That, not withstanding, it's always the "edge cases" that cause panic stricken calls at 3 in the morning.
If I have to worry about dates that far back, I'll convert everything to datetme2 since this is a SQL Server 2008 forum. That way I can go back further. Only problem then is what calendar do you really use? Heck, if I remember correctly we used different calendars into the early 1900's didn't we?
December 4, 2012 at 5:57 pm
sgmunson (12/4/2012)
Lynn Pettis (12/4/2012)
Compare:
DECLARE @date DATE = '20121202';
SELECT
CASE WHEN datename(dw,@date) <> 'Sunday' THEN CONVERT(varchar(10), DATEADD(WK, DATEDIFF(WK, 0, @date), 0),
103) ELSE CONVERT(varchar(10), DATEADD(WK, DATEDIFF(WK, 0, @date), - 7), 103) END
go
DECLARE @date DATE = '20121203';
SELECT
CASE WHEN datename(dw,@date) <> 'Sunday' THEN CONVERT(varchar(10), DATEADD(WK, DATEDIFF(WK, 0, @date), 0),
103) ELSE CONVERT(varchar(10), DATEADD(WK, DATEDIFF(WK, 0, @date), - 7), 103) END
go
DECLARE @date DATE = '20121202';
select dateadd(wk, datediff(wk, 0, DATEADD(dd,-1,@Date)), 0);
GO
DECLARE @date DATE = '20121203';
select dateadd(wk, datediff(wk, 0, DATEADD(dd,-1,@Date)), 0);
GO
Yup, you have it right. I didn't get to see your post before I posted my answer. I'd sure like to understand a bit more about why that works. It's using the number of weeks since date zero through yesterday, then re-adding the same number of weeks to date zero, which suggests that perhaps the week is defined based on Monday ? Or is it Sunday, and thus why yesterday is used instead of today?
Why the code I posted returns this weeks Monday date is because 1900-01-01 is a Monday. I subtracted 1 in the calculations on tthis thread because the OP waanted the Sunday prior to the Monday date in the previous week. If it is Monday, and I subtract 1 from it and perform the the calculation used in my posts, it returns the Monday of that week.
I would suggest playing with the date calculatioins and see what other tidbits you might discover. If you go to my blog on ssc, you will find more date calculations there as well.
December 4, 2012 at 10:26 pm
Lynn Pettis (12/4/2012)
Jeff Moden (12/4/2012)
Then substitute a 0 for Michael's -53690. 😀That, not withstanding, it's always the "edge cases" that cause panic stricken calls at 3 in the morning.
If I have to worry about dates that far back, I'll convert everything to datetme2 since this is a SQL Server 2008 forum. That way I can go back further. Only problem then is what calendar do you really use? Heck, if I remember correctly we used different calendars into the early 1900's didn't we?
Since not all countries adopted the Gregorian calendar at the same time and the Julian calendar (which was mathematically incorrect compared to Earth's orbit) was in effect before that, I suspect a simple switch to DATETIME2 would produce some good inaccuracies.
Just to be sure, I wasn't challenging you. I meant only to explain that Michaeal does with dates what a lot of us (including you and me) do with a lot of other things... make the code mostly bullet-proof in the face of future unknown domains and scalability especially since we don't know who's going to use our code for what once they find it.
Still, I'm like you in that I prefer to use the 0 date reference for a couple of reasons. It's shorter to type, requires literally "0" memory for me to remember, and allows for negative math without running into the "bottom" of the allowed timeline.
Shifting gears, I've always wanted to know what the performance of the integer trick that Micheal (I use it too because it's easy for me to remember) uses for such week calculation and the methods that avoid direct math on dates. Admittedly, both methods are very fast and it takes millions of rows (which I work with in about a 3rd of all the queries I have to write) to actually see a difference but every millisecond counts if you have to do such a thing with many columns across many tables for such a thing. I've long suspected that the integer method would squeak past (9% faster in this case) the 3 part method but have never taken the time to prove it until now.
Here's the test code. No... I don't trust SET STATISTICS any more.
--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#SomeTable','U') IS NOT NULL
DROP TABLE #SomeTable
;
--===== Build and ppulate the test table on-the-fly.
SELECT TOP 5000000
SomeDate = DATEADD(dd,ABS(CHECKSUM(NEWID()))%DATEDIFF(dd,'1900','2000'),'1900')
INTO #SomeTable
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
GO
PRINT '--===== Performance Baseline Simple Select Method =====--'
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
DECLARE @Bitbucket DATETIME,
@StartTime DATETIME;
SELECT @StartTime = GETDATE();
SELECT @Bitbucket = SomeDate
FROM #SomeTable
;
PRINT DATEDIFF(ms,@StartTime, GETDATE());
GO
PRINT '--===== Performance -53690 Integer Math Function Method =====--'
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
DECLARE @Bitbucket DATETIME,
@StartTime DATETIME;
SELECT @StartTime = GETDATE();
SELECT @Bitbucket = DATEADD(dd,(DATEDIFF(dd,-53690,SomeDate)/7)*7,-53690)
FROM #SomeTable
;
PRINT DATEDIFF(ms,@StartTime, GETDATE());
GO
PRINT '--===== Performance 0 Integer Math Function Method =====--'
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
DECLARE @Bitbucket DATETIME,
@StartTime DATETIME;
SELECT @StartTime = GETDATE();
SELECT @Bitbucket = DATEADD(dd,(DATEDIFF(dd,0,SomeDate)/7)*7,0)
FROM #SomeTable
;
PRINT DATEDIFF(ms,@StartTime, GETDATE());
GO
PRINT '--===== Performance 3 DATETIME Function Method =====--'
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
DECLARE @Bitbucket DATETIME,
@StartTime DATETIME;
SELECT @StartTime = GETDATE();
SELECT @Bitbucket = DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd,-1,SomeDate)), 0)
FROM #SomeTable
;
PRINT DATEDIFF(ms,@StartTime, GETDATE());
GO
Here are the run results on my humble 2005 desktop box.
--===== Performance Baseline Simple Select Method =====--
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
2850
--===== Performance -53690 Integer Math Function Method =====--
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
4830
--===== Performance 0 Integer Math Function Method =====--
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
4833
--===== Performance 3 DATETIME Function Method =====--
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
5263
--Jeff Moden
December 5, 2012 at 2:01 am
Thanks all for ur reply!
December 5, 2012 at 7:51 am
Jeff Moden (12/4/2012)
Lynn Pettis (12/4/2012)
Jeff Moden (12/4/2012)
Then substitute a 0 for Michael's -53690. 😀That, not withstanding, it's always the "edge cases" that cause panic stricken calls at 3 in the morning.
If I have to worry about dates that far back, I'll convert everything to datetme2 since this is a SQL Server 2008 forum. That way I can go back further. Only problem then is what calendar do you really use? Heck, if I remember correctly we used different calendars into the early 1900's didn't we?
Since not all countries adopted the Gregorian calendar at the same time and the Julian calendar (which was mathematically incorrect compared to Earth's orbit) was in effect before that, I suspect a simple switch to DATETIME2 would produce some good inaccuracies.
Just to be sure, I wasn't challenging you. I meant only to explain that Michaeal does with dates what a lot of us (including you and me) do with a lot of other things... make the code mostly bullet-proof in the face of future unknown domains and scalability especially since we don't know who's going to use our code for what once they find it.
Still, I'm like you in that I prefer to use the 0 date reference for a couple of reasons. It's shorter to type, requires literally "0" memory for me to remember, and allows for negative math without running into the "bottom" of the allowed timeline.
Shifting gears, I've always wanted to know what the performance of the integer trick that Micheal (I use it too because it's easy for me to remember) uses for such week calculation and the methods that avoid direct math on dates. Admittedly, both methods are very fast and it takes millions of rows (which I work with in about a 3rd of all the queries I have to write) to actually see a difference but every millisecond counts if you have to do such a thing with many columns across many tables for such a thing. I've long suspected that the integer method would squeak past (9% faster in this case) the 3 part method but have never taken the time to prove it until now.
Here's the test code. No... I don't trust SET STATISTICS any more.
--===== Conditionally drop the test table to make reruns in SSMS easier.IF OBJECT_ID('tempdb..#SomeTable','U') IS NOT NULL
DROP TABLE #SomeTable
;
--===== Build and ppulate the test table on-the-fly.
SELECT TOP 5000000
SomeDate = DATEADD(dd,ABS(CHECKSUM(NEWID()))%DATEDIFF(dd,'1900','2000'),'1900')
INTO #SomeTable
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
GO
PRINT '--===== Performance Baseline Simple Select Method =====--'
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
DECLARE @Bitbucket DATETIME,
@StartTime DATETIME;
SELECT @StartTime = GETDATE();
SELECT @Bitbucket = SomeDate
FROM #SomeTable
;
PRINT DATEDIFF(ms,@StartTime, GETDATE());
GO
PRINT '--===== Performance -53690 Integer Math Function Method =====--'
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
DECLARE @Bitbucket DATETIME,
@StartTime DATETIME;
SELECT @StartTime = GETDATE();
SELECT @Bitbucket = DATEADD(dd,(DATEDIFF(dd,-53690,SomeDate)/7)*7,-53690)
FROM #SomeTable
;
PRINT DATEDIFF(ms,@StartTime, GETDATE());
GO
PRINT '--===== Performance 0 Integer Math Function Method =====--'
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
DECLARE @Bitbucket DATETIME,
@StartTime DATETIME;
SELECT @StartTime = GETDATE();
SELECT @Bitbucket = DATEADD(dd,(DATEDIFF(dd,0,SomeDate)/7)*7,0)
FROM #SomeTable
;
PRINT DATEDIFF(ms,@StartTime, GETDATE());
GO
PRINT '--===== Performance 3 DATETIME Function Method =====--'
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
DECLARE @Bitbucket DATETIME,
@StartTime DATETIME;
SELECT @StartTime = GETDATE();
SELECT @Bitbucket = DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd,-1,SomeDate)), 0)
FROM #SomeTable
;
PRINT DATEDIFF(ms,@StartTime, GETDATE());
GO
Here are the run results on my humble 2005 desktop box.
--===== Performance Baseline Simple Select Method =====--
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
2850
--===== Performance -53690 Integer Math Function Method =====--
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
4830
--===== Performance 0 Integer Math Function Method =====--
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
4833
--===== Performance 3 DATETIME Function Method =====--
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
5263
Jeff,
I know you weren't challenging me, that is part of the problem with text based communication, you miss out on tone of voice and body language for thos subtle clues as to the real meaning of what is said.
With that, however, you are constantly challenging me to become better at what I do. In another thread I said you were the Mentor of the Year. That's wrong. I should have said the Mentor of the Years as you you have done this for many years. I can honestly say you have been a big part of my learning since 2005. You and several others on ssc to be sure. I just hope I have been able to help others as much as you have.
December 5, 2012 at 3:54 pm
The reason I use -53690, instead of 0 is simple: It produces correct results for the entire range of possible datetime values.
You can view the difference in the query results below. The calculation is wrong for dates before 1900-01-01 when you use 0.
FYI, -53690 = 1753-01-01, the earliest possible datetime value, so if it makes it easier to remember, you can use that as illustrated in the query below.
In case you wonder why datetime starts 1753-01-01: England and its colonies adopted the Gregorian calendar in mid 1752 and 1753 was the first full year that used the Gregorian calendar. Other countries adopted it earlier or later.
select
SomeDate,
[Weekday]= left(datename(dw,SomeDate),9),
[Date -53690]=
dateadd(dd,(datediff(dd,-53690,SomeDate)/7)*7,-53690),
[Date 17530101]=
dateadd(dd,(datediff(dd,'17530101',SomeDate)/7)*7,'17530101'),
[Date 0]=
dateadd(dd,(datediff(dd,0,SomeDate)/7)*7,0)
from
(-- Test Data
select SomeDate = convert(datetime,'18900504')union all
select SomeDate = '18900505'union all
select SomeDate = '18900506'union all
select SomeDate = '18900507'union all
select SomeDate = '18900508'union all
select SomeDate = '18900509'union all
select SomeDate = '18900510'union all
select SomeDate = '18900511'union all
select SomeDate = '18900512'union all
select SomeDate = '18900513'union all
select SomeDate = '18900514'
) a
order by
a.SomeDate
Results:
SomeDate Weekday Date -53690 Date 17530101 Date 0
----------------------- --------- ----------------------- ----------------------- -----------------------
1890-05-04 00:00:00.000 Sunday 1890-04-28 00:00:00.000 1890-04-28 00:00:00.000 1890-05-05 00:00:00.000
1890-05-05 00:00:00.000 Monday 1890-05-05 00:00:00.000 1890-05-05 00:00:00.000 1890-05-05 00:00:00.000
1890-05-06 00:00:00.000 Tuesday 1890-05-05 00:00:00.000 1890-05-05 00:00:00.000 1890-05-12 00:00:00.000
1890-05-07 00:00:00.000 Wednesday 1890-05-05 00:00:00.000 1890-05-05 00:00:00.000 1890-05-12 00:00:00.000
1890-05-08 00:00:00.000 Thursday 1890-05-05 00:00:00.000 1890-05-05 00:00:00.000 1890-05-12 00:00:00.000
1890-05-09 00:00:00.000 Friday 1890-05-05 00:00:00.000 1890-05-05 00:00:00.000 1890-05-12 00:00:00.000
1890-05-10 00:00:00.000 Saturday 1890-05-05 00:00:00.000 1890-05-05 00:00:00.000 1890-05-12 00:00:00.000
1890-05-11 00:00:00.000 Sunday 1890-05-05 00:00:00.000 1890-05-05 00:00:00.000 1890-05-12 00:00:00.000
1890-05-12 00:00:00.000 Monday 1890-05-12 00:00:00.000 1890-05-12 00:00:00.000 1890-05-12 00:00:00.000
1890-05-13 00:00:00.000 Tuesday 1890-05-12 00:00:00.000 1890-05-12 00:00:00.000 1890-05-19 00:00:00.000
1890-05-14 00:00:00.000 Wednesday 1890-05-12 00:00:00.000 1890-05-12 00:00:00.000 1890-05-19 00:00:00.000
Note that if you are not starting the week on a Monday, you may need additional code for the edge conditions:
select
SomeDate,
[Weekday]= left(datename(dw,SomeDate),9),
[Sunday]=
case
-- There is no possible datetime Sunday before 1753-01-07
when SomeDate < '17530107' then null
else
dateadd(dd,(datediff(dd,'17530107',SomeDate)/7)*7,'17530107')
end
from
(-- Test Data
select SomeDate = convert(datetime,'17530101')union all
select SomeDate = convert(datetime,'17530102')union all
select SomeDate = convert(datetime,'17530103')union all
select SomeDate = convert(datetime,'17530104')union all
select SomeDate = convert(datetime,'17530105')union all
select SomeDate = convert(datetime,'17530106')union all
select SomeDate = convert(datetime,'17530107')union all
select SomeDate = convert(datetime,'17530108')
) a
order by
a.SomeDate
Results:
SomeDate Weekday Sunday
----------------------- --------- -----------------------
1753-01-01 00:00:00.000 Monday NULL
1753-01-02 00:00:00.000 Tuesday NULL
1753-01-03 00:00:00.000 Wednesday NULL
1753-01-04 00:00:00.000 Thursday NULL
1753-01-05 00:00:00.000 Friday NULL
1753-01-06 00:00:00.000 Saturday NULL
1753-01-07 00:00:00.000 Sunday 1753-01-07 00:00:00.000
1753-01-08 00:00:00.000 Monday 1753-01-07 00:00:00.000
Sample of code to handle edge conditions for any start day of week:
select
a.DATE,
[DayOfWeek] = left(datename(dw,a.DATE),9),
Mon =dateadd(dd,(datediff(dd,-53690,a.DATE)/7)*7,-53690),
Tue =case when a.DATE >= -53689 then
dateadd(dd,(datediff(dd,-53689,a.DATE)/7)*7,-53689) end,
Wed =case when a.DATE >= -53688 then
dateadd(dd,(datediff(dd,-53688,a.DATE)/7)*7,-53688) end,
Thu =case when a.DATE >= -53687 then
dateadd(dd,(datediff(dd,-53687,a.DATE)/7)*7,-53687) end,
Fri =case when a.DATE >= -53686 then
dateadd(dd,(datediff(dd,-53686,a.DATE)/7)*7,-53686) end,
Sat =case when a.DATE >= -53685 then
dateadd(dd,(datediff(dd,-53685,a.DATE)/7)*7,-53685) end,
Sun =case when a.DATE >= -53684 then
dateadd(dd,(datediff(dd,-53684,a.DATE)/7)*7,-53684) end
from
(-- Test Data
select Date = convert(datetime,'17530101')union all
select Date = convert(datetime,'17530102')union all
select Date = convert(datetime,'17530103')union all
select Date = convert(datetime,'17530104')union all
select Date = convert(datetime,'17530105')union all
select Date = convert(datetime,'17530106')union all
select Date = convert(datetime,'17530107')union all
select Date = convert(datetime,'17530108')union all
select Date = convert(datetime,'99991231')
) a
order by
a.DATE
This function will give the correct results for any datetime value for any day of the week you want to start with:
Start of Week Function:
December 6, 2012 at 6:06 am
select cast(case datepart(dw,Loaddate)
when 1 then dateadd(ww,-0,Loaddate-6)
when 2 then dateadd(ww,-0,Loaddate)
when 3 then dateadd(ww,-0,Loaddate-1)
when 4 then dateadd(ww,-0,Loaddate-2)
when 5 then dateadd(ww,-0,Loaddate-3)
when 6 then dateadd(ww,-0,Loaddate-4)
when 7 then dateadd(ww,-0,Loaddate-5)
end as date)
December 6, 2012 at 8:16 am
Ulysses_Infinity (12/6/2012)
select cast(case datepart(dw,Loaddate)
when 1 then dateadd(ww,-0,Loaddate-6)
when 2 then dateadd(ww,-0,Loaddate)
when 3 then dateadd(ww,-0,Loaddate-1)
when 4 then dateadd(ww,-0,Loaddate-2)
when 5 then dateadd(ww,-0,Loaddate-3)
when 6 then dateadd(ww,-0,Loaddate-4)
when 7 then dateadd(ww,-0,Loaddate-5)
end as date)
This only produces correct results when the setting of DATEFIRST is 7 (Sunday)
SET DATEFIRST 7
December 6, 2012 at 5:07 pm
Lynn Pettis (12/5/2012)
I know you weren't challenging me, that is part of the problem with text based communication, you miss out on tone of voice and body language for thos subtle clues as to the real meaning of what is said.With that, however, you are constantly challenging me to become better at what I do. In another thread I said you were the Mentor of the Year. That's wrong. I should have said the Mentor of the Years as you you have done this for many years. I can honestly say you have been a big part of my learning since 2005. You and several others on ssc to be sure. I just hope I have been able to help others as much as you have.
Gosh. :blush: Thanks, Lynn. I can only hope to live up to such a high compliment.
I agree. Text based communication is a bit tough. That's part of the reason why I wanted to make sure you knew I wasn't challenging but rather just providing info.
Keep up the good work! You're definitely a heavy hitter and definitely one of the good guys. You've helped more people than I think you realize.
--Jeff Moden
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply