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