Efficient Datediff calculation

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

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

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

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

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

<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='' />] No biggie.

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

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

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

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

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

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