SQL Server Performance

Efficient Datediff calculation

Discussion in 'Performance Tuning for DBAs' started by PAMUR, Dec 2, 2005.

  1. PAMUR New Member

    Dear All,

    I need to calculate Current Date - Date of Join in months, exact till date, and perform query on that to restrict rows based on number of months > than 20 etc. What is the best performance oriented way to do this. Pleaaaaaaaaaaaaaaaaaaaaaaase suggest.

    Usha

    Usha Rani
  2. dineshasanka Moderator

  3. Madhivanan Moderator

    Is this?

    Select columns from yourTable where DateDiff(Month,FromDate,ToDate)>20

    Otherwise post some sample data and the result you want

    Madhivanan

    Failing to plan is Planning to fail
  4. SQLDBcontrol New Member

    declare @date datetime

    set @date = dateadd(month,-20,getdate()

    select datediff(month, getdate(),join_date) as months_since_joined
    from table1
    where join_date <= @date --I'm assuming that number of months > 20 means that the number of months since joined is > 20

    I don't know what you mean by "exact till date".

    The reason I defined the @date parameter is because you want to avoid using a function (dateadd in this case) within the WHERE clause of a T-SQL statement because SQL Server will perform a table scan instead of use any available indexes otherwise.

    Hope this helps.



    Karl Grambow

    www.sqldbcontrol.com
  5. FrankKalis Moderator

    SQL Server *can* use an index even when DATEADD is in the WHERE clause. I think, that's the only DATETIME function which is able to do so. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] <br />&lt; shameless plug &gt;<br /<a target="_blank" href=http://www.sql-server-performance.com/fk_datetime.asp>http://www.sql-server-performance.com/fk_datetime.asp</a><br />&lt; /shameless plug &gt;<br /><br />Usha,<br />please post DDL, sample data and required output. These informations should improve your chances to gedt a quick and good response.<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  6. PAMUR New Member



    Dear All, Thanks. Sorry! I haven't posted my query in the begining.

    Select DATEDIFF(mm,'05-30-2002',Getdate()), returns 43 months but it should be 42 months.

    The query is:
    Select g_user_id, d_date_join, DATEDIFF(mm,d_date_join,Getdate())As MonthsSinceJoined
    From MemberDetail
    where d_date_join < DATEADD(mm,-21,GETDATE()).

    If I am to derive 42 months( Exact months till date), I need to check on number of days remaining to be a month and baced on business logic I can treat > 15 days remining as one month or totally forget remaining days.

    I need Performance oriented suggestion.

    Thank You.


    Usha Rani
  7. Madhivanan Moderator

    Select DATEDIFF(day,'05-30-2002',Getdate())/30

    Madhivanan

    Failing to plan is Planning to fail
  8. PAMUR New Member

    Hi, Frank you are soooooooooooo sweeeeeet. Thanks for the article.

    Dear Madhivanan,

    Select DATEDIFF(day,'05-18-2002',Getdate())/30 -- returns 43 ahouldn't it be 42. Sorry! to annoy you.

    Thanks

    Usha Rani
  9. Madhivanan Moderator


    Select DATEDIFF(day,'05-18-2002',Getdate())/30

    Run it again. It will return 42
    Which version of SQL Server are you using?


    Madhivanan

    Failing to plan is Planning to fail
  10. FrankKalis Moderator

    Not exactly sure, if I understood your requirement, but what about


    DECLARE @StartDate DATETIME
    DECLARE @EndDate DATETIME
    SELECT @StartDate = '20020530', @EndDate = '20051202'

    SELECT
    CASE
    WHEN DATEADD(MONTH,DATEDIFF(MONTH,@StartDate,@EndDate),@StartDate) >= @EndDate
    THEN
    CASE
    WHEN DATEADD(DAY, 15, @EndDate) > DATEADD(MONTH,DATEDIFF(MONTH,@StartDate,@EndDate),@StartDate)
    THEN DATEDIFF(MONTH,@StartDate,@EndDate)
    ELSE DATEDIFF(MONTH,@StartDate,@EndDate) - 1
    END
    ELSE
    CASE
    WHEN DATEADD(DAY, - 15, @EndDate) > DATEADD(MONTH,DATEDIFF(MONTH,@StartDate,@EndDate),@StartDate)
    THEN DATEDIFF(MONTH,@StartDate,@EndDate) +1
    ELSE DATEDIFF(MONTH,@StartDate,@EndDate)
    END
    END

    -----------
    42

    (1 row(s) affected)

    When you change @EndDate to


    @EndDate = '20051215'

    it will still return 42. The change in months is done for any day greater than 15. If you need to include the 15th of a month for the break, you need to change this to


    DECLARE @StartDate DATETIME
    DECLARE @EndDate DATETIME
    SELECT @StartDate = '20020530', @EndDate = '20051215'

    SELECT
    CASE
    WHEN DATEADD(MONTH,DATEDIFF(MONTH,@StartDate,@EndDate),@StartDate) >= @EndDate
    THEN
    CASE
    WHEN DATEADD(DAY, 16, @EndDate) > DATEADD(MONTH,DATEDIFF(MONTH,@StartDate,@EndDate),@StartDate)
    THEN DATEDIFF(MONTH,@StartDate,@EndDate)
    ELSE DATEDIFF(MONTH,@StartDate,@EndDate) - 1
    END
    ELSE
    CASE
    WHEN DATEADD(DAY, - 16, @EndDate) > DATEADD(MONTH,DATEDIFF(MONTH,@StartDate,@EndDate),@StartDate)
    THEN DATEDIFF(MONTH,@StartDate,@EndDate) +1
    ELSE DATEDIFF(MONTH,@StartDate,@EndDate)
    END
    END

    -----------
    43

    (1 row(s) affected)

    Though I'm not sure if this is the "best" solution for your problem anyway.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  11. Madhivanan Moderator

    Frank, what is wrong with Select DATEDIFF(day,'05-18-2002',Getdate())/30?

    Madhivanan

    Failing to plan is Planning to fail
  12. Adriaan New Member

    Why use 30? There are 4 months that have 30 days, 7 that have 31 days, and one that has either 28 or 29 days.

    The correct option should be to use MONTH as the 'datepart' parameter.

    You probably won't notice the error until you have to cover greater periods of time:

    select DATEDIFF(DAY,'2000-07-01', '2005-12-02')/30 ---> 66
    select DATEDIFF(MONTH,'2000-07-01', '2005-12-02') ---> 65
  13. Madhivanan Moderator

    Well<br /><br />&gt;&gt;The correct option should be to use MONTH as the 'datepart' parameter.<br /><br />Thats what my first reply is [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  14. Adriaan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />Well<br /><br />&gt;&gt;The correct option should be to use MONTH as the 'datepart' parameter.<br /><br />Thats what my first reply is [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">Okay, just pointing out the error, in case you were only testing for shorter periods of time.[<img src='/community/emoticons/emotion-2.gif' alt=':D' />] No biggie.
  15. Adriaan New Member

    ... and perhaps another suggestion:

    If the client is not satisfied with the results expressed as the nr of months, then they shouldn't be looking at the nr of months in the first place.

    Nr of weeks at least gives them an unambiguous nr of days, or even working days, for the remainder.

    Nr of days, or working days, must be the best option.
  16. PAMUR New Member

    Still
    Select DATEDIFF(Month,'2002-05-20', '2005-12-02') - 43 instead of 42 using SQL Server 2000.

    Frank! we can do as you suggested based on the cutoff number of days to include as a month. But if I need this to run for say 1000 rows won't the performance be effected?

    More over I need to check for the number of months elapesed since the date of join of the member in a where clause. Months elapsed > 45. I have a field d_Date_Joined datetime storing only date not time.



    Usha Rani
  17. SQLDBcontrol New Member

    Interesting, I didn't know that. Good article by the way.<br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />SQL Server *can* use an index even when DATEADD is in the WHERE clause. I think, that's the only DATETIME function which is able to do so. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] <br />&lt; shameless plug &gt;<br /<a target="_blank" href=http://www.sql-server-performance.com/fk_datetime.asp>http://www.sql-server-performance.com/fk_datetime.asp</a><br />&lt; /shameless plug &gt;<br /><br />Usha,<br />please post DDL, sample data and required output. These informations should improve your chances to gedt a quick and good response.<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Karl Grambow<br /><br />www.sqldbcontrol.com
  18. Adriaan New Member

    quote:Originally posted by PAMUR

    Still
    Select DATEDIFF(Month,'2002-05-20', '2005-12-02') - 43 instead of 42 using SQL Server 2000.

    Frank! we can do as you suggested based on the cutoff number of days to include as a month. But if I need this to run for say 1000 rows won't the performance be effected?

    More over I need to check for the number of months elapesed since the date of join of the member in a where clause. Months elapsed > 45. I have a field d_Date_Joined datetime storing only date not time.



    Usha Rani
    Okay then, the correction is for when the day-of-the-month of the end date is before the day-of-the-month of the start date ... then wouldn't this be the simplest solution:

    DECLARE @Dt1 DATETIME, @Dt2 DATETIME, @StartDt DATETIME, @EndDt DATETIME

    SET @Dt1 = '2002-05-20'
    SET @Dt2 = '2005-12-02'

    -- This takes care of whichever date is before the other
    IF @Dt1 > @Dt2
    SELECT @StartDt = @Dt2, @EndDt = @Dt1
    ELSE
    SELECT @StartDt = @Dt1, @EndDt = @Dt2

    SELECT DATEDIFF(Month, @StartDt, @EndDt)
    + CASE WHEN DAY(@EndDt) < DAY(@StartDt) THEN -1 ELSE 0 END
  19. rohit2900 Member

    Hi All,
    Can any body explain me the difference between the below where conditions.
    where getdate() >= (set_d - 28)
    where DATEDIFF(DAY,SET_D,GETDATE()) < 28
  20. Adriaan New Member

    You should avoid expressions in your WHERE clause that do a calculation on the column. If there is an index on set_d, it will be ignored if the expression does any calculation on the column value.
    So try to keep set_d on one side of the comparison, and the calculation on the other.
    WHERE (GETDATE() + 28) >= set_d

Share This Page