Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Performance Test of New Date Manipulation Functions (SQL Spackle) Expand / Collapse
Author
Message
Posted Thursday, May 10, 2012 9:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 10:14 PM
Points: 5,364, Visits: 8,952
Jeff Moden (5/10/2012)
As usual, awesome article, Wayne.

It is a bit amazing to me that MS isn't, for some reason, able to do performance wise at the base code level what we're able to do at the "hack" level. I was really shocked when you test the previous row stuff against the Quirky Update. They should spend more time on stuff like making Pivot as useful as it is in Access. On second thought, it's already slow enough. Maybe they should leave it alone.


Thanks Jeff.

One thing to keep in mind is that these functions are not designed to truncate parts of a date. (Well, maybe Format is.) Everything else going on here is using existing functions to truncate a date down to a specific part. I'm sure that if MS ever builds a "TruncateDate" function, it will be fast (though internally it might just do the dateadd/datediff thing?).

So, what is being tested is utilizing one or more functions to accomplish a specific task which these functions were not explicitly designed to perform, but they might be used to accomplish the task. DateAdd/DateDiff - neither is designed for truncating a datetime value. DateTimeFromParts is designed to build a datetime from values, not to truncate. Format could be argued that it is designed to do so, but I see it more as a report formatting tool since it doesn't leave the result in the original data type.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1297951
Posted Friday, May 11, 2012 1:28 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:02 PM
Points: 7,801, Visits: 9,553
Terrific article, Wayne.

It would be nice to see a comparison using datetime2, datetimeoffset, date (for relevant truncations only) and time (again for relevant truncations only) as well as datetime. Of course there is hordes of legacy data that uses the old datatype, which means including it is essential, but it's a pity to leave the others out. Maybe doing it for smalldatetime too would also be useful.


Tom
Post #1298921
Posted Tuesday, May 15, 2012 7:37 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 5:30 PM
Points: 851, Visits: 241
Thanks for the post Wayne, I will point all developers of convoluted datetime function hacks to this article for education.
I always find simplicity in code to be a beautiful thing, can I make a small suggestion on the DateAdd function you use?
We use

DATEADD(MONTH, DATEDIFF(MONTH, 0, SomeDate), 0) -- as inspired by Rob Farley

and make use of the implicit conversion of 0 to a base date. We also use the value to add offsets to the date calculation,
so I'm thinking the new functionality may well go unused here.
Post #1300711
Posted Sunday, July 1, 2012 6:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 10:14 PM
Points: 5,364, Visits: 8,952
craig.watson (5/15/2012)
Thanks for the post Wayne, I will point all developers of convoluted datetime function hacks to this article for education.
I always find simplicity in code to be a beautiful thing, can I make a small suggestion on the DateAdd function you use?
We use

DATEADD(MONTH, DATEDIFF(MONTH, 0, SomeDate), 0) -- as inspired by Rob Farley

and make use of the implicit conversion of 0 to a base date. We also use the value to add offsets to the date calculation,
so I'm thinking the new functionality may well go unused here.


This does work, but only for the DATETIME and SMALLDATETIME data types. For the newer date data types introduced in SQL 2008, this will fail. In order to be consistent, I use date strings. And lately, I use ISO date strings to avoid date conversion issues. So, I end up with:
DATEADD(MONTH, DATEDIFF(MONTH, '1900-01-01T00:00:00', SomeDate), '1900-01-01T00:00:00')



Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1323600
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse