Home Forums Article Discussions Article Discussions by Author Discuss content posted by Wayne Sheffield Performance Test of New Date Manipulation Functions (SQL Spackle) RE: Performance Test of New Date Manipulation Functions (SQL Spackle)

  • July 1, 2012 at 6:22 pm

    #1507577

    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
    Author - SQL Server T-SQL Recipes

    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2