Date Manipulation with DATEADD/DATEDIFF

  • Garadin (4/7/2010)


    Thanks.

    Nothing like actually having an article published that lets you see all the things that you forgot to add to it! :hehe:

    I thought of the same thing with my first article publication. It is a nice learning tool.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • And what about the old CONVERT(VARCHAR(12),GETDATE(),101), will this work in some cases?

  • I concur... thanks Seth, great article and I have added these tips and documentation to my notes.

  • Hi, Great to see all the ways to manipulate dates in SQL.

    Here is another way of doing it rather quickly.

    SELECT CAST(CAST(GETDATE() AS CHAR(11)) AS DATETIME)

    Cool stuff,

    Hanri

    Whenever I get sad, I stop being sad and be awesome instead… true story!

  • hi,

    You can change the first day of the week by using the @@DATEFIRST keyword.

    http://msdn.microsoft.com/en-us/library/ms181598(SQL.90).aspx

    Regards,

    Hanri

    Whenever I get sad, I stop being sad and be awesome instead… true story!

  • SQLJeff (4/8/2010)


    And what about the old CONVERT(VARCHAR(12),GETDATE(),101), will this work in some cases?

    Most likely... but the problem with that is as I previously stated... it uses twice as much CPU time and takes twice as long duration wise. If you're only working with a handful of rows, you certainly won't notice the difference. BUT, if you're working with many millions of rows like I usually have to, combined with other CPU saving methods, it makes all the difference in the world. Every microsecond counts for the stuff I usually have to do.

    --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

  • Great article, Seth! Seeing as how I've tried method after method for accomplishing this and I've never been pleased with what I've done, I think this is clever, elegant, and darned handy. Thanks for sharing!

    Regards,

    Mike M

  • Hanri Naude (4/8/2010)


    hi,

    You can change the first day of the week by using the @@DATEFIRST keyword.

    http://msdn.microsoft.com/en-us/library/ms181598(SQL.90).aspx

    Regards,

    Hanri

    You can change the day of the week, but as I mentioned in an earlier comment, DATEDIFF is not affected by DATEFIRST settings. I'll add a section about that into the article when I get a moment and can submit that and a few other changes.

    Also, as Jeff mentioned, converting to a char/varchar and then back to datetime is considerably slower... but we're talking about fractions of a second per row, so if you're only doing a couple it's not a big deal. For instance, doing anything to GETDATE() to store in a variable... not gonna make much of a difference. But If you need to do it to the column of a table with a million rows, you'll definitely see the difference.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Is this because SQL Server stores dates as a numeric value? Then CONVERT to VARCHAR() would cause an implicit conversion?

  • Thank you for the article. This function has always thrown me off on SQL server.

    I dreaded dates before this! Thanks again.

    --
    :hehe:

  • lhowe (4/7/2010)


    Another method to strip the time from a date/time value and keep it as a datetime type would be: CONVERT(datetime,CONVERT(varchar,GetDate(),101))

    This is probably the best generic method for rounding datetime - by using varchar(N) for vaious values of N and with different type arguments instead of 101 it can round down to second, minute, hour, month, or yearas well as to day. Of course it uses more CPU than the method in the article, and that will be important sometimes.

    Tom

Viewing 11 posts - 31 through 41 (of 41 total)

You must be logged in to reply to this topic. Login to reply