Date Manipulation with DATEADD/DATEDIFF

  • Comments posted to this topic are about the item Date Manipulation with DATEADD/DATEDIFF

    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]

  • Great handy article !!

    However, we can also achieve the same through -

    SELECT CONVERT(DATETIME,DATEDIFF(dd,0,GETDATE()))

    This way we can save the time taken to perform DATEADD operation though it would be quite marginal....

    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post.
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

  • the fastest way to remove time from datetime is:

    select CONVERT(datetime,floor(convert(float,getdate())))

    Itzik Ben-Gan had a big post on Date Manipulation with benchmarking..(long time ago..)

  • For some common and uncommon date formats:

    http://www.sql-server-helper.com/tips/date-formats.aspx

    That site also has code for finding first/last day of month, week, and so on.

  • Nice article Seth. I will be adding this article to my arsenal.

    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

  • Hi

    I also use CAST(GETDATE() AS date) ... but it works only for sql server 2008 because in an earlier version date is not a type. Another point of view is that a smalldatetime use some bites for storing time even it is 00:00:000. The "date" type is more proper for this case.

    ____________________________________________________________________
    Catalin Dumitru
    For personal info please visit www.catalin-dumitru.ro

  • Good article, well presented, will probably implement some of it.

    Thanks!

    Glen Parker 🙂

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

  • Neat! Will definitely use it.

  • On my system using the query for the first day of the week gives Monday instead of Sunday which is, of course, incorrect. Is this based on the locale of the system, or is that consistent behavior all together. After all, the first day of the week is always Sunday not Monday.

    SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)

    query performed on 2010-04-07 08:42

    query results in 2010-04-05 instead of expected 2010-04-04.

    My locale should be en_US.

  • This is a great article with very good followup comments. I have been working with SSRS for almost three years now, and every time i need some weird or exotic formula for the Sales Reports (Date requirements include today and the last 2 Months as well as same time last year!) I have to look up different formulas. I will definitely use this article in my SQL activities. Thank you for the time and effort as well as the great contribution!:-)

  • nakache (4/6/2010)


    the fastest way to remove time from datetime is:

    select CONVERT(datetime,floor(convert(float,getdate())))

    Itzik Ben-Gan had a big post on Date Manipulation with benchmarking..(long time ago..)

    I believe this is the article you were referring to? In there, he actually mentions that his favorite technique is the DADD method, although his tests show casting as an int to be very slightly faster.

    Here's a performance evaluation[/url] by Gail Shaw on her blog of the different methods.

    I won't make any claims as to which of these is faster, I'm sure they each win on occasion, but for the versatility, I'll stick with Dateadd/Datediff.

    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]

  • mbarkell (4/7/2010)


    On my system using the query for the first day of the week gives Monday instead of Sunday which is, of course, incorrect. Is this based on the locale of the system, or is that consistent behavior all together. After all, the first day of the week is always Sunday not Monday.

    mbarkell,

    check @@DATEFIRST (Transact-SQL) in BOL...

    Arkware

  • Thank you, I thought it might be a sortof locale setting. @@DateFirst is a handy thing to keep in mind.

  • Very handy and nicely done article. I've come across situtations where I've needed to manipulate dates and will definitely keep this article handy. 🙂

Viewing 15 posts - 1 through 15 (of 41 total)

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