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

Use DATEDIFF which
Returns the number of date and time boundaries crossed between two specified dates.


----------------------------------------
http://spaces.msn.com/members/dineshasanka

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

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

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. []
< shameless plug >
http://www.sql-server-performance.com/fk_datetime.asp
< /shameless plug >

Usha,
please post DDL, sample data and required output. These informations should improve your chances to gedt a quick and good response.

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

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

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

Madhivanan

Failing to plan is Planning to fail

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

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

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)

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

Madhivanan

Failing to plan is Planning to fail

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

Well

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

Thats what my first reply is []

Madhivanan

Failing to plan is Planning to fail

quote:

---

Originally posted by Madhivanan

Well

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

Thats what my first reply is []

Madhivanan

Failing to plan is Planning to fail

---

Okay, just pointing out the error, in case you were only testing for shorter periods of time.[] 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.