Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Wayne Sheffield » Performance Test of New Date Manipulation...
Add to briefcase
14 posts, Page 2 of 2
««12

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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:50 AM
Points: 6,367, Visits: 8,227
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
L' Eomot Inversé
Posted Friday, May 11, 2012 1:28 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:32 PM
Points: 7,080, Visits: 7,125
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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)
Post #1298921
craig.watson
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: Monday, May 06, 2013 10:32 PM
Points: 851, Visits: 202
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
WayneS
Posted Sunday, July 01, 2012 6:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:50 AM
Points: 6,367, Visits: 8,227
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
14 posts, Page 2 of 2
««12

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.