July 30, 2003 at 6:40 am
quote:
I thought it wasDATEADD(d,DATEDIFF(d,0,dtCol),0)
makes no difference. DateAdd has the following prototype:
DATEADD ( datepart , number, date )
SQL implicitly converts the INT returned by DATEDIFF to a DATE if you put the DATEDIFF in the second part. It implicitly converts the number 0 to the DATE '1900-01-01 00:00:00' if you put the number 0 last. Either way, one implicit conversion and the same result...
As far as the CAST(FLOOR(CAST())) method goes, it will always be slower than DATEADD(DATEDIFF()) because it needs 1 more execution for each row read. To prove this, I set up the following test on a test database, where tblWorkOrder contained ~250000 records:
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
--
DBCC DROPCLEANBUFFERS
--
SET STATISTICS TIME ON
--
SELECT TOP 10 DATEADD(day , DATEDIFF(day, 0, dtmLastEdited), 0) as "Date", COUNT(*)
FROM tblWorkOrder
GROUP BY DATEADD(day, DATEDIFF(day, 0, dtmLastEdited), 0 )
ORDER BY "Date" DESC
--
SET STATISTICS TIME OFF
--
DBCC DROPCLEANBUFFERS
--
SET STATISTICS TIME ON
--
SELECT TOP 10 CAST(FLOOR(CAST (dtmLastEdited AS real)) AS smalldatetime) as "Date", COUNT(*)
FROM tblWorkOrder
GROUP BY CAST(FLOOR(CAST (dtmLastEdited AS real)) AS smalldatetime)
ORDER BY "Date" DESC
--
SET STATISTICS TIME OFF
--
DBCC DROPCLEANBUFFERS
--
SET STATISTICS TIME ON
--
SELECT TOP 10 CAST(FLOOR(CAST (dtmLastEdited AS real)) AS smalldatetime) as "Date", COUNT(*)
FROM tblWorkOrder
GROUP BY CAST(FLOOR(CAST (dtmLastEdited AS real)) AS smalldatetime)
ORDER BY "Date" DESC
--
SET STATISTICS TIME OFF
--
DBCC DROPCLEANBUFFERS
--
SET STATISTICS TIME ON
--
SELECT TOP 10 DATEADD(day , DATEDIFF(day, 0, dtmLastEdited), 0) as "Date", COUNT(*)
FROM tblWorkOrder
GROUP BY DATEADD(day, DATEDIFF(day, 0, dtmLastEdited), 0 )
ORDER BY "Date" DESC
--
SET STATISTICS TIME OFF
I repeated the test twice, reversing the order of the calls to ensure that I wasn't getting skewed results from a cache anywhere. The results, in both iterations were that DATEDIFF() method was 2293 ms versus the CAST(FLOOR(CAST())) method taking 3205 ms. Time results were identical for both iterations. These results were exactly what would be expected (~33% gain), considering the second method has 33% more function calls.
July 30, 2003 at 6:50 am
Interesting. I'll use that myself in future.
I tried it with 5 rows and it was inconclusive, too busy to try with any more
Cheers
Shawn
July 30, 2003 at 7:18 am
how about...
Group by Convert(char, your_date_field, 101)
July 30, 2003 at 7:22 am
I found this snippet in the SQL Books Online, SP3 version, in the reference for LIKE:
It is recommended that LIKE be used when you search for datetime values, because datetime entries can contain a variety of dateparts. For example, if you insert the value 19981231 9:20 into a column named arrival_time, the clause WHERE arrival_time = 9:20 cannot find an exact match for the 9:20 string because SQL Server converts it to Jan 1, 1900 9:20AM. A match is found, however, by the clause WHERE arrival_time LIKE '%9:20%'.
Now, this syntax seems fairly finicky, but after a little tinkering I was able to get the following to work:
select createdate from t_agent_schedule_intervals where createdate like 'May%1%2003%'
“In anything at all, perfection is finally attained not when there is no
longer anything to add, but when there is no longer anything to take away.”
Saint-Exupéry
Wind, Sand, and Stars
“In anything at all, perfection is finally attained not when there is no
longer anything to add, but when there is no longer anything to take away.”
Saint-Exupéry
Wind, Sand, and Stars
July 30, 2003 at 7:51 am
I read some articles a long time back, got some whacked out ideas, and wrote this function. I remember testing it a fair amount, but I got distracted and never got around to deploying it. (I recalled the code while reading this thread.)
Rather than convert the datetime to real or float (with the possible performance overhead of datatype conversion) this converts it to binary--which is presumably faster, an assertion I can't back up (lack of testing).
Again, I never finished testing this, and (like the REAL/FLOAT strategies) it relies on the physical implementation. If anyone can prove or finish it, please post the results!
Philip
/*******************************************************************************
**
** Function: fnDateOnly
** Description: Take a datetime OR smalldatetime value and truncate the time
** portion (convert it to 00:00, as in "midnight the morning of" the date)
**
** How it works:
** - If the value passed in is (4 byte) smalldatetime, it is first converted
** to (8 byte) datetime
** - It is then converted to binary(8). Of course, no actual data conversion
** is really necessary, as it already is an 8 byte value
** - The last four bytes are replaced by 0x0 (i.e., no time past midnight)
** - The binary value is converted back to date time--again, requiring no
** actual conversion of data
** - On return, if necessary the the datatype is converted back to smalldatetime
**
**
** Input parameters: Date/time to be converted
**
** Return values: Input parameter with the time portion set to 00:00[:00.000]
**
********************************************************************************
** Version History
********************************************************************************
** Date: Author: Description:
** ---------- -------- -------------------------------------------
** 10/10/2002 PKelley Created, if not used or implemented
**
********************************************************************************/
CREATE FUNCTION dbo.fnDateOnly
(@Date datetime)
returns datetime
AS
BEGIN
RETURN cast(substring(cast(@date as binary(8)),1,4) + 0x00000000 as datetime)
END
July 30, 2003 at 9:12 am
quote:
how about...Group by Convert(char, your_date_field, 101)
This does an extra lookup in syslanguages to find the localization for date format. See page 1 of this thread.
July 30, 2003 at 9:15 am
This will return the date with the time seemingly stripped off (set to 00:00:00)
Select Convert(datetime, Convert(char, GetDate(),101)
returns 2003-07-30 00:00:00.000
hth
July 30, 2003 at 9:22 am
what's a few nano-seconds between friends?
July 30, 2003 at 9:22 am
One thing to remember is that the date is actually stored as a decimal so there is no real "conversion" when going to a float or int. I have run tests to compare the performance through many millions of iterations and find the DATEADD( DATEDIFF functions mentioned at the beginning to easily be the best performers and the most accurate (no rounding). The other routines all work also - it just depends on the requirements you have in the system.
Guarddata-
July 30, 2003 at 3:21 pm
Philip, I took some time and tested your function against DateDiff-DateAdd.
CREATE FUNCTION dbo.fnDateOnlyBinary
(@Date datetime)
returns datetime
AS
BEGIN
RETURN cast(substring(cast(@date as binary(8)),1,4) + 0x00000000 as datetime)
END
CREATE FUNCTION dbo.fnDateOnly_DateDiff
(@Date datetime)
returns datetime
AS
BEGIN
RETURN DATEADD(d,DATEDIFF(d,0,@Date),0)
END
Here were the results on a million dates.
--Time: 55
--CPU:13312
--IO:116
select top 1000000 dbo.fnDateOnlyBinary(DT)
From #Date
--Time: 55
--CPU:11063
--IO:234
select top 1000000 dbo.fnDateOnly_DateDiff(DT)
From #Date
Your function used about 25% more cpu cycles, but only half the IO. It appears that Datediff and DateAdd cost IO but save a little on CPU, while casting and substring costs no IO and more CPU.
I'm thinking I'll replace my current "day only"
function with this one, as IO is a bottleneck for us. Of course, even the datediff-dateadd method is better than the cast-convert I'm using now.
Good stuff.
Signature is NULL
July 31, 2003 at 8:24 am
Calivin, glad you found it useful. It would probably use less CPU if it used bitmasking, which was my original idea.
I think I dropped that path because you could only do so with integers, and those are 4 bytes and I didn't want to mess with bigint (that or we were still on SQL 7.0). Fortuitously, I forgot all about that while messing about just now... because it seems that if you do
CAST(@dateVal as int)
SQL keeps the 4 bytes storing the date and truncates those holding the time, and casting that back as datetime produces 00:00 ("midnight the day of")... meaning you can drop all that concatenation chaff and use:
cast((cast(@date as int) as datetime)
That's got to use less CPU, right?
Philip
July 31, 2003 at 9:27 am
For those of you who saw my previous posting, which showed that the CAST method beat the DATEDIFF method, sorry, I deleted the post once I realized that the CAST to INT on smalldatetime data was producing incorrect results. Whe I ran this:
select top 10
DATEADD(day , DATEDIFF(day, 0, dtmLastEdited), 0) as "DateDiff Date"
, CAST(CAST(dtmLastEdited AS int) AS smalldatetime) as "Cast Date"
, dtmLastEdited as "True Date"
from tblWorkOrder
I got these results, so appararently, CAST to INT with smalldatetimes is unreliable:
DateDiff Date Cast Date True Date
----------------------- ------------------- ------------------
2001-05-02 00:00:00.000 2001-05-02 00:00:00 2001-05-02 08:46:00
2003-03-05 00:00:00.000 2003-03-06 00:00:00 2003-03-05 15:24:00
2002-09-23 00:00:00.000 2002-09-24 00:00:00 2002-09-23 12:04:00
2002-09-23 00:00:00.000 2002-09-24 00:00:00 2002-09-23 12:02:00
2002-03-04 00:00:00.000 2002-03-04 00:00:00 2002-03-04 09:16:00
2003-05-05 00:00:00.000 2003-05-05 00:00:00 2003-05-05 09:38:00
2003-05-05 00:00:00.000 2003-05-05 00:00:00 2003-05-05 09:40:00
2003-06-30 00:00:00.000 2003-06-30 00:00:00 2003-06-30 10:41:00
2003-05-28 00:00:00.000 2003-05-29 00:00:00 2003-05-28 15:47:00
2003-05-28 00:00:00.000 2003-05-29 00:00:00 2003-05-28 15:49:00
I think DATEDIFF is still the best accurate way to strip the time...it looks like the cast to INT on a SMALLDATETIME takes into account the first hour portion of the date...
Edited by - jpipes on 07/31/2003 09:30:07 AM
July 31, 2003 at 10:14 am
The way I heard it (in an article by Kalen Delaney in SQL Server magazine over a year ago), the internal storage of datetime is something like:
DateTime: 8 bytes total
-- "first" 4 bytes count number of days since "zero" (Jan 1, 1753)
-- "Second" 4 bytes count number of thousandths of seconds (maybe 333rds of seconds?) since midnight
SmallDateTime: 4 bytes total
-- "first" 2 bytes count number of days since "zero" (Jan 1, 1900?)
-- "Second" 2 bytes count number of minutes since midnight
Thus my hacking around with truncations and bitmasks at the binary level. Big problem: this relies on Microsoft not changing their internal coding structures or data formats. There's just no way they'd change that in a service pack, but it may break with major upgrades (Yukon).
Me, I can't wait for date-only and time-only data types, as that will remove all these problems once and for all (for the small price of the upgrade...)
Philip
July 31, 2003 at 12:31 pm
Philip, It uses less CPU, but it has rounding issues just like all the other "Cast(Cast(@date as int) as datetime)". Any time over 12 Noon and the day is rounded up.
However...
cast(substring(cast(@date as binary(8)),1,4) + 0x00000000 as datetime)
...Has no rounding issues at all.
And, as long as you put your code in a function and use it consistently, conversion shouldn't be an issue if Microsoft decides to change their date formats.
Signature is NULL
August 1, 2003 at 3:57 am
quote:
Philip, I took some time and tested your function against DateDiff-DateAdd.CREATE FUNCTION dbo.fnDateOnlyBinary
(@Date datetime)
returns datetime
AS
BEGIN
RETURN cast(substring(cast(@date as binary(8)),1,4) + 0x00000000 as datetime)
END
CREATE FUNCTION dbo.fnDateOnly_DateDiff
(@Date datetime)
returns datetime
AS
BEGIN
RETURN DATEADD(d,DATEDIFF(d,0,@Date),0)
END
Here were the results on a million dates.
--Time: 55
--CPU:13312
--IO:116
select top 1000000 dbo.fnDateOnlyBinary(DT)
From #Date
--Time: 55
--CPU:11063
--IO:234
select top 1000000 dbo.fnDateOnly_DateDiff(DT)
From #Date
Your function used about 25% more cpu cycles, but only half the IO. It appears that Datediff and DateAdd cost IO but save a little on CPU, while casting and substring costs no IO and more CPU.
I'm thinking I'll replace my current "day only"
function with this one, as IO is a bottleneck for us. Of course, even the datediff-dateadd method is better than the cast-convert I'm using now.
Good stuff.
I tested this yesturday while waiting for a return from an Oracle system and found both methods to have almost exact comparison even when the system cache was cleared or the server restarted. Sometimes the DATEADD/DATEDIFF method came up a hair better on duration but both gave (at least on my test system) almost exact same out for CPU, IO and Duration. I tested with datasets of 100, 1000, 10000, 50000, 1000000, and 10000000 and all were neck and neck. I will try to capture the results when I hae a chance for record and post.
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply