Calculating Dates

I have a table in my sql server that calculates renewal dates for me, that date is based on the final suit date. The table has the ssn, and the FinalSuitDate its in a one to many relationship with the employeetable.

the Finalsuit table is suppose to calculate the renewal dates(which I'm trying to do in a query) my original expression in access was using dateserial FirstRenewal: DateSerial(Year([FINALSUITDONE])+2,Month([FINALSUITDONE])+1,Day([FINALSUITDONE])=30), but sql does not recognize that.

For Example

If the Finalsuit is 12/01/2000
then the renewal would be 12/31/2002
the renewal are to be done on the last day of the month and two years from the finalsuit date. Problems is I'm having problems doing this in sql servers query?

Can someone out there help pleaseI think I've got the syntax right on this, but I just had a beer so can't be (hic) sure:

dateadd(day, -1, convert(char(10), dateadd(month, 25, [FINALSUITDONE]), 120)+'-01')

Adds 25 months to [FINALSUITDONE] to get the month FOLLOWING the month you want. Then CONVERTS to the first day of that month, and the subtracts 1 day to get that last day of the month 24 months (2 years) from the date you started with.|||thank you for replying, but it gave me an error message saying "your entry can not be converted to a valid datetime entry"

So frustrated

thank you for trying to help though I sure do appreciate it.
back to the books|||do this SELECT CAST(FINALSUITDONE AS DATETIME) FROM table. See if that gives you an error. If it does, you need to find out whatever entry is not a proper datetime and fix it before anything you do like this will work.|||No, do this:

1) Wait 2 hours after drinking a beer before posting any forum answers.
2) Post the correct syntax:

dateadd(day, -1, convert(char(7), dateadd(month, 25, [FINALSUITDONE]), 120)+'-01')

...should have use "convert(char(7)..." instead of "convert(char(10)..." :o|||hmmmmmmmm, IT'S BRIIIIIILIANT.|||Brilliant!|||Thank you so much Blindman for your help, I was really starting to get stressed out. By the way your very funny, I'll take your advice and have a couple of beers.

Wish I had your knowledge, still working towards it|||What a drunken genious

We have a lot of them here ... :D