SQL Server 2012 introduces several new date/time functions that allow you to build a date/time from the individual parts of a date/time value. As I was experimenting with them, I was thinking that this is pretty neat, and bound to be very useful. And then I started wondering how well it performed.
Before I had time to test it, I was working on something else where I needed to truncate a date/time value to the first day of the month. I immediately whipped out my toolbelt – it has the routine that I learned from Lynn Pettis a few years back on SQLServerCentral.com from his blog post on Common Date Routines. This routine does the DateAdd(MONTH, DateDiff(MONTH, ‘1900-01-01’, @Date), ‘1900-01-01’) trick so see how many month boundaries exist from a known date to the tested date, and then to add this number of month boundaries back to the known date. With the month datepart being used, the result ends up being the first of the month of the tested date. Using other dateparts gives us other dates with remainders being truncated.
As I was looking at that wonderfully efficient code, I realized that I could get the same results with the DATETIMEFROMPARTS function in SQL Server 2012. So I coded an example, and it worked beautifully.
And now, I had a burning question. Performance-wise, just how do these different methods of truncating dates compare against each other? So, stop everything that I’m doing, and code a test.
First things first. A performance test requires a bunch of test data. And I mean a bunch. So, I grabbed some code from an article that Jeff Moden wrote on SQLServerCentral.com at the beginning of 2011. Sure enough, there’s a million row dataset of random dates… just what I’m looking for. So, by borrowing some code from Jeff, I have my test data:
|
1 2 3 4 5 6 |
SELECT TOP 1000000 RowNum = IDENTITY(INT,1,1), SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME) INTO dbo.DateTest FROM sys.All_Columns t1, sys.All_Columns t2; |
Next up is to generate a test and to produce timings. In the following code, I truncate each date to the first day of the year, month and quarter, and then I truncate the time down to the hour, minute, and second. I perform this conversion twice, the first time using Lynn’s DateAdd/DateDiff code, and the second time by stripping out the various parts of the date, and putting it back together with the DATETIMEFROMPARTS function. Both queries dump the results into temp tables. I run this block of code with SET STATISTICS TIME ON just before each query to capture the time to run each query, and that batch is performed 25 times.
For the DateAdd/DateDiff, each calculation is counting the number of boundaries being crossed. Each boundary that we’re truncating the date to has to be counted individually. To use the SQL Server 2012 DATETIMEFROMPARTS function, we first have to extract each appropriate part before they can be passed back into the function to build the date; this code extracts it out once for each record in the CROSS APPLY, and then uses that value in each of the functions.
First up, the code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
DECLARE @Base DATETIME = '1900-01-01', @Base2 DATETIME = '2000-01-01'; IF OBJECT_ID('tempdb..#test1','U') IS NOT NULL DROP TABLE #test1; IF OBJECT_ID('tempdb..#test2','U') IS NOT NULL DROP TABLE #test2; RAISERROR ('DateAdd/DateDiff test', 10, 1) WITH NOWAIT; SET STATISTICS TIME ON; SELECT SomeDate, DATEADD(YEAR, DATEDIFF(YEAR, @Base, SomeDate), @Base) AS [FirstDayOfYear], DATEADD(MONTH, DATEDIFF(MONTH, @Base, SomeDate), @Base) AS [FirstDayOfMonth], DATEADD(QUARTER, DATEDIFF(QUARTER, @Base, SomeDate), @Base) AS [FirstDayOfQuarter], DATEADD(HOUR, DATEDIFF(HOUR, @Base, SomeDate), @Base) AS StartOfHour, DATEADD(MINUTE, DATEDIFF(MINUTE, @Base, SomeDate), @Base) AS StartOfMinute, DATEADD(SECOND, DATEDIFF(SECOND, @Base2, SomeDate), @Base2) AS StartOfSecond INTO #test1 FROM dbo.DateTest; SET STATISTICS TIME OFF; RAISERROR ('DateTimeFromParts test', 10, 1) WITH NOWAIT; SET STATISTICS TIME ON; SELECT SomeDate, DATETIMEFROMPARTS(ca.Yr, 1, 1, 0, 0, 0, 0) AS [FirstDayOfYear], DATETIMEFROMPARTS(ca.Yr, ca.Mth, 1, 0, 0, 0, 0) AS [FirstDayOfMonth], DATETIMEFROMPARTS(ca.Yr, ca.Qtr, 1, 0, 0, 0, 0) AS [FirstDayOfQuarter], DATETIMEFROMPARTS(ca.Yr, ca.Mth, ca.Dy, ca.Hr, 0, 0, 0) AS StartOfHour, DATETIMEFROMPARTS(ca.Yr, ca.Mth, ca.Dy, ca.Hr, ca.Mn, 0, 0) AS StartOfMinute, DATETIMEFROMPARTS(ca.Yr, ca.Mth, ca.Dy, ca.Hr, ca.Mn, ca.Sec, 0) AS StartOfSecond INTO #test2 FROM dbo.DateTest dt CROSS APPLY (SELECT DATEPART(YEAR, dt.SomeDate) AS Yr, DATEPART(MONTH, dt.SomeDate) AS Mth, ((CEILING(MONTH(dt.SomeDate)/3.0)*3)-2) AS Qtr, DATEPART(DAY, dt.SomeDate) AS Dy, DATEPART(HOUR, dt.SomeDate) AS Hr, DATEPART(MINUTE, dt.SomeDate) AS Mn, DATEPART(SECOND, dt.SomeDate) AS Sec ) ca; SET STATISTICS TIME OFF; GO 25 |
I then took the results, and used a split function to get the timings from each query into a result set. This was coped into Excel, where the following line graphs tell the rest of the story:
I think I’ll continue to use Lynn’s DateAdd/DateDiff method when I need to truncate a date.
(Test environment: Windows 7 x64; SQL Server 2012 x64; 8GB ram)
If you really want to see slow, try using FORMAT 🙂 It is a heck of a lot easier to code, but it is 2 orders of magnitude slower, and I didn’t even try to do the quarter computation. On just one run with only 10000 rows, it took over 4 seconds…
RAISERROR (‘Format test’, 10, 1) WITH NOWAIT;
SET STATISTICS TIME ON;
SELECT SomeDate, –‘dd/MM/yyyy’
CAST(FORMAT (SomeDate, ‘yyyy-01-01’) AS DATETIME) AS [FirstDayOfYear],
CAST(FORMAT (SomeDate, ‘yyyy-MM-01’) AS DATETIME) AS [FirstDayOfMonth],
DATEADD(QUARTER, DATEDIFF(QUARTER, @Base, SomeDate), @Base) AS [FirstDayOfQuarter],
CAST(FORMAT (SomeDate, ‘yyyy-MM-dd HH:00:00’) AS DATETIME) AS StartOfHour,
CAST(FORMAT (SomeDate, ‘yyyy-MM-dd HH:mm:00’) AS DATETIME) AS StartOfMinute,
CAST(FORMAT (SomeDate, ‘yyyy-MM-d HH:mm:ss’) AS DATETIME) AS StartOfSecond
INTO #test3
FROM dbo.DateTest;
SET STATISTICS TIME OFF;
Reply