COnverting Crystal date formula in SQL server 2005

[Zara9]

Hi,
I have been stuck at this for sometime. I will appreciate if someone helps me. I have a date of birth column and one age column. In the final result I would like to see members turning 18 this year starting from today so today would be (1st of month...August 1st 2010). In crystal, the formula is: Year({table1.bdate}) <= Year(CurrentDate) - 18
Question is: how do I write this in sql.
I have something like this: (DATEDIFF(year, bdate, DATEADD(s, - 1, DATEADD(mm,DATEDIFF(m, 0, GETDATE()), 0))) = 18)
But this give me results of members turning 18 for the whole year including MArch, April etc.

 

[IanWaterman]

Zara

Year is a SQL server function too.

Year(bdate) <= Year(getdate()) - 18

Ian

 

[Zara9]

Ian thanks for replying. When I do use the Year(bdate) <= Year(getdate()) - 18
It gives me all the members with age greater than 18.

What I want is, members turning 18 this year starting from the current month. So, for this month it would start from August 1st.

and i dont know how to get it.. Plz help
Zara

 

[markros]

Where BDate >= dateadd(year, -18, getdate())


PluralSight Learning Library

 

[Zara9]

Markros:

This give me members with age 18 and less..
I only want to see members turning 18 this year (starting from the current month)

 

[Zara9]

(DATEDIFF(year, bdate, DATEADD(s, - 1, DATEADD(mm,DATEDIFF(m, 0, GETDATE()), 0))) = 18)

This gives me closest results but only problem is that it starts from January not from current month.

Any suggestion to this one?

 

[markros]

Ok, try
BDate <= dateadd(year, -18, getdate()) and year(BDate) = 18
and Month(BDate)>=Month(getdate())

PluralSight Learning Library

 

[Zara9]

Thanks so much for helping me but this gives me nothing, no result at all.

hmm..

 

[markros]

Typing faster than brain I meant to switch to >= (instead of <=)

PluralSight Learning Library

 

[Zara9]

Still the same doesnt give me any result :/