Calculating last day of month

[Irishiii75]

I am using SQL server 2000. I have a query that finds people who will turn age 21 in two months. So in Nov 2001, I am finding people who has a date of birth 01/01/1981-01/31/1981. The query is set up to find those who have birthdays were +2 months, -21 years from current date. I need to create a field called term date, which is to be the last day of the month in which they turn age 21. In my example above, I need the query to calculate the term date 1/31/2002. Next month, it show calculate the term date 02/28/2002. I am having trouble with this. Any suggestings using their date of birth as a field in the table?

Thanks

 

[tlbroadbent]

Create a UDF as follows.

Create function fn_LastDateOfMonth (@date as datetime)
Returns datetime
As

--Example: Find last day of month two month in future
--Select dbo.fn_LastDateOfMonth(dateadd(mm,2,getdate()))

Begin

Set @date=convert(varchar(11),dateadd(mm,1,@date))
Set @date=dateadd(dd,-day(@date),@date)
Return(@date)

End Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.