Obtain the date of datetime field
-
Jeff: Thanks for the test harness and profiler results. Just out of curiosity, I tested a CONVERT/DATEDIFF variation in your test harness and it appears to be a little faster than the DATEADD/DATEDIFF method. I thought you might find this interesting if you get the same results.
[font="Courier New"]--===== Setup a million row test table in a nice safe place that everyone has
USE TempDB;
GO
SELECT TOP 1000000
SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)
INTO dbo.JBMTest
FROM Master.sys.ALL_Columns t1,
Master.sys.ALL_Columns t2; --Lack of join criteria makes this a CROSS-JOIN
GO
--===== DATEADD/DATEDIFF
DECLARE @Bitbucket DATETIME; --Takes display time out of the picture
SELECT @Bitbucket = DATEADD(DAY, DATEDIFF(DAY, 0, SomeDate), 0)
FROM dbo.JBMTest
OPTION (MAXDOP 1); --Takes parallelism out of the picture
GO
--===== DOUBLE CONVERT
DECLARE @Bitbucket DATETIME; --Takes display time out of the picture
SELECT @Bitbucket = CONVERT(DATETIME, CONVERT(NVARCHAR, SomeDate,103), 103)
FROM dbo.JBMTest
OPTION (MAXDOP 1); --Takes parallelism out of the picture
GO
--===== CONVERT/DATEDIFF
DECLARE @Bitbucket DATETIME; --Takes display time out of the picture
SELECT @Bitbucket = CONVERT(DATETIME, datediff(day, 0, SomeDate))
FROM dbo.JBMTest
OPTION (MAXDOP 1); --Takes parallelism out of the picture
GO
--===== Housekeeping
DROP TABLE dbo.JBMTest;
GO[/font]
-
Mike Mullen (6/8/2010)
Jeff: Thanks for the test harness and profiler results. Just out of curiosity, I tested a CONVERT/DATEDIFF variation in your test harness and it appears to be a little faster than the DATEADD/DATEDIFF method. I thought you might find this interesting if you get the same results.Interesting and I did get similar results. I also threw in CAST/DATEDIFF which takes turns winning with CONVERT/DATEDIFF although CONVERT/DATEDIFF appears to be the more frequent winner. Every milli-second helps. 🙂
--===== Setup a million row test table in a nice safe place that everyone hasUSE TempDB;
GO
SELECT TOP 1000000
SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)
INTO dbo.JBMTest
FROM Master.sys.ALL_Columns t1,
Master.sys.ALL_Columns t2; --Lack of join criteria makes this a CROSS-JOIN
GO
--===== DATEADD/DATEDIFF
DECLARE @Bitbucket DATETIME; --Takes display time out of the picture
SELECT @Bitbucket = DATEADD(DAY, DATEDIFF(DAY, 0, SomeDate), 0)
FROM dbo.JBMTest
OPTION (MAXDOP 1); --Takes parallelism out of the picture
GO
--===== DOUBLE CONVERT
DECLARE @Bitbucket DATETIME; --Takes display time out of the picture
SELECT @Bitbucket = CONVERT(DATETIME, CONVERT(NVARCHAR, SomeDate,103), 103)
FROM dbo.JBMTest
OPTION (MAXDOP 1); --Takes parallelism out of the picture
GO
--===== CONVERT/DATEDIFF
DECLARE @Bitbucket DATETIME; --Takes display time out of the picture
SELECT @Bitbucket = CONVERT(DATETIME, datediff(day, 0, SomeDate))
FROM dbo.JBMTest
OPTION (MAXDOP 1); --Takes parallelism out of the picture
GO
--===== CAST/DATEDIFF
DECLARE @Bitbucket DATETIME; --Takes display time out of the picture
SELECT @Bitbucket = CAST(datediff(day, 0, SomeDate) AS DATETIME)
FROM dbo.JBMTest
OPTION (MAXDOP 1); --Takes parallelism out of the picture
GO
--===== Housekeeping
DROP TABLE dbo.JBMTest;
GO
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Oleg Netchaev (6/7/2010)
There is no way I can possible dislike when my post is commented by you Jeff. When I wrote that I liked that you picked on my post, I actually meant it. My introduction to T-SQL began from reading Ken Henderson's (may he rest in peace) T-SQL book for version 7, and it went downhill from there. Though it was a number of years ago, I know that there is much more to learn, so your comments are much appreciated.Oleg
Gosh, Oleg. Thanks for the nice comments. I was definitely on the edge of taking it the wrong way. Thank you very much for the clarification. I'm humbled. :blush:
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply