Finding users in a table who are close to the age 18

[joseph.larrew]

Hey all, just joined up and I'm excited to see if I can get my problem resolved. Now, I am pretty new to databases and SQL, so you might have to be pretty basic with me. I'm trying to query a table with a bunch of birthdates in it, and I'm trying to find records that are 3 months away from turning 18. I've been trying to use the DateDiff function as follows:

SELECT Patient.[Patient ID], Patient.[Last Name], Patient.[First Name], Patient.[Date of Birth]
FROM Patient
WHERE DateDiff("yyyy", Date(), Patient.[Date of Birth]...)

And that's where I'm kinda stuck. I can't really say "=18" or "<18" or anything like that. I'm trying to find those who fall within a 3 month window. Any ideas? I'm sure it can be done.

Thanks,
Joseph

 

[pbaldy]

The first thing that comes to mind is to calculate months instead of years, and then:

WHERE DateDiff(...) Between x And y

where x and y are the number of months that meet your needs.

 

[Kryst51]

How about years like this: Between 17.75 and 18

 

[joseph.larrew]

Hey Guys, thanks for the replies. I've been away from the client that I've been doing this work for, so I haven't been on here in a while. What I need is for the query to compare the current date with the birthdate of my records in my table. If the number of months is greater than 212 months (17.75 years), then return that record.

 

[pbaldy]

So, have you changed the DateDiff to return months as I suggested?

 

[joseph.larrew]

Another question I should ask is how do I store the returned value so I can compare it to 212 months?

 

[joseph.larrew]

I'm clueless on the datediff function really. I know the parameters I have to input, but I don't know how to use it.

 

[pbaldy]

I also demonstrated how to use it, though if you simply want greater than something it would be

WHERE DateDiff(...) > 212

 

[joseph.larrew]

So something like:

select [fields from the table]
from [table name]
where datediff('m', table.[date of birth], Now()) > 212

Is that the correct syntax?

 

[joseph.larrew]

Wait, I just typed into my query what I typed up there and it worked....... I swear it didn't before!!!! AhHHH! That's relatively frustrating....

 

[joseph.larrew]

I knew it had to be simple, I just knew it!

 

[pbaldy]

Glad you got it working!