Date Manipulation with DATEADD/DATEDIFF

  • vinaypugalia (4/6/2010)


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

    An interesting idea. It does seem to perform slightly better. Here's a proof using my 200K row Tally Table.

    SELECT DATEADD(d, DATEDIFF(d,0,GETDATE()), 0) A

    INTO #2

    FROM Util..Tally

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

    INTO #1

    FROM Util..Tally

    DROP TABLE #1

    DROP TABLE #2

    (200000 row(s) affected)

    SQL Server Execution Times:

    CPU time = 78 ms, elapsed time = 95 ms.

    (200000 row(s) affected)

    SQL Server Execution Times:

    CPU time = 62 ms, elapsed time = 93 ms.

    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]

  • If you are loading the date value into a datetime variable or a datetime column you only need to do the DATEDIFF.

    DECLARE @dtDate datetime

    SET @dtDate = datediff(d,0,getdate())

    PRINT @dtDate

    If you want to display the date value directly in a select then you'll need to convert it back to a datetime in some manner like the ones show in the article or in this thread. Note this method of stripping the time only works because SQL Server internally stores dates as numbers. If SQL Server were to change how it internally stores dates as numbers well then....who knows if these methods would work.

  • Seth,

    very nice. good explaination.

    I have one question...

    in the following code, isn't it returning the first of the week +/- 2 days?

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

    --: 2010-03-01 00:00:00.000 Start of the day 2 days from now

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

    --: 2010-02-25 00:00:00.000 Start of the day 2 days ago.

    Thanks,

    Arkware

  • ArkWare (4/7/2010)


    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

    Great Question.

    This is a tricky one. DATEDIFF doesn't actually honor datefirst or locale settings. Here's an article (also by Itzik Ben-Gan) that talks about it.

    The reason you're getting monday is because 1/1/1900 was a monday. To adjust that particular query to get a sunday instead, you can use this:

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

    I should probably add that into the article.

    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]

  • ArkWare (4/7/2010)


    Seth,

    very nice. good explaination.

    I have one question...

    in the following code, isn't it returning the first of the week +/- 2 days?

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

    --: 2010-03-01 00:00:00.000 Start of the day 2 days from now

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

    --: 2010-02-25 00:00:00.000 Start of the day 2 days ago.

    Thanks,

    Arkware

    It is. That was a copy/paste error. Sorry about that. Those wk's should be d's.

    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]

  • Sorry - duplicate post (someone just beat me to it!) 🙂

    Good article!

    I think there might be some typos in the examples for finding dates in the past & future....this is what was shown:

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

    --: 2010-03-01 00:00:00.000 Start of the day 2 days from now

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

    --: 2010-02-25 00:00:00.000 Start of the day 2 days ago.

    I believe it should be:

    SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 2)

    --: 2010-03-01 00:00:00.000 Start of the day 2 days from now

    SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), -2)

    --: 2010-02-25 00:00:00.000 Start of the day 2 days ago.

  • Thanks for taking the time. I've stored it in a handy place.

  • Date manipulation is always something I have to lookup. Hopefully this can help me remember it so I can just write it.

    Thanks,

  • SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 2) will produce today's date not Start of the day 2 days from now as you stated.

    Good idea to have scripts for dates

  • using dd with

    SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 2) will give you two days from now

  • I frequently use SELECT CAST(FLOOR(CAST(<yourDateHere> AS FLOAT)) AS DATETIME)

    Interestingly, rounding happens near midnight. The following returns '2010-04-08 00:00:00.000':

    DECLARE @Date DATETIME = '2010-04-07 23:59:59.999'

    SELECT CAST(FLOOR(CAST(@Date AS FLOAT)) AS DATETIME)

    Not sure if this timestamp would actually happen...

  • Elaine Shafer-401422 (4/7/2010)


    I frequently use SELECT CAST(FLOOR(CAST(<yourDateHere> AS FLOAT)) AS DATETIME)

    Interestingly, rounding happens near midnight. The following returns '2010-04-08 00:00:00.000':

    DECLARE @Date DATETIME = '2010-04-07 23:59:59.999'

    SELECT CAST(FLOOR(CAST(@Date AS FLOAT)) AS DATETIME)

    Not sure if this timestamp would actually happen...

    As mentioned in the article, Datetime is only accurate to 3ms. 999 and 998 both round up to 000. It's not your float conversion doing the rounding, it's the limitations of the datetime data type. Run this:

    DECLARE @Date DATETIME = '2010-04-07 23:59:59.999'

    SELECT @date

    SELECT CAST(FLOOR(CAST(@Date AS FLOAT)) AS DATETIME)

    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]

  • Thanks for a good article. I've been using the CONVERT(CONVERT) technique mentioned by lhowe.

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

    The value of the DADD technique is the ability to apply it to weeks, months, years.

    Thanks again!

  • Nice done, Seth. Good clear article. Heh... it's funny how these things happen... I just got done demonstrating to the folks at work how VARCHAR conversions use twice the CPU that DADD conversions 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

  • Thanks.

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

    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]

Viewing 15 posts - 16 through 30 (of 41 total)

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