current age

I need two examples of current age in sql.  How can I select all the people in my tblregistrations who are currently 17 and under based on month and also based on date?

Does this work for month?
select * from tblregistrations where FLOOR(DATEDIFF(day, DOB, Month(servertime)) / 365.25) < 18

Does this work for date?
select * from tblregistrations where FLOOR(DATEDIFF(day, DOB, '7/1/2014') / 365.25) < 18
al4629740Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
PortletPaulConnect With a Mentor Commented:
select * 
from tblregistrations
WHERE  YEAR('20140701') - YEAR(DOB)
        - ( CASE
               WHEN MONTH(DOB) > MONTH('20140701')
                 OR ( MONTH(DOB) = MONTH('20140701')
                    AND DAY(DOB) > DAY('20140701')
                    )
               THEN 1
               ELSE 0
             END )  < 18
             

Open in new window


to me 7/1/2014 = 7th Jan 2014, so I see it in YYYYMMDD as this: '20140107'
I suspect you really want '20140701'

just replace
getdate()

with
'20140701'

OR

declare @adate as date = '20140701'

then replace getdate() with @adate
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
you would need month and year to calculate age.  Month in and of itself tells you nothing.

the 365.25 also doesn't work.  I know you're trying to compensate for leap year but you'll get into rounding issues.

What exactly are you trying to do?
0
 
PortletPaulCommented:
Please try this:
select * 
from tblregistrations
WHERE  YEAR(getdate()) - YEAR(DOB)
        - ( CASE
               WHEN MONTH(DOB) > MONTH(getdate())
                 OR ( MONTH(DOB) = MONTH(getdate())
                    AND DAY(DOB) > DAY(getdate())
                    )
               THEN 1
               ELSE 0
             END )  < 18
             

Open in new window


+edit:
absolutely agree with Kyle anything based on 365.25 is NOT a reliable method
see: MSSQL: Calculating Age (in years) :: right -v- wrong
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
al4629740Author Commented:
There are two different sql statements that I'm working on.

I am trying to retrieve the records for everyone less than 18 based on the servertime for the one.

For the second query, I am trying to get all less than 18 based on a specified DATE. ie 7/1/2014
0
 
al4629740Author Commented:
Curiously, how much extra "query time" does it take to run these type of calculated queries?  Is it heavy?
0
 
PortletPaulCommented:
It's not an ideal filter condition and it will add some effort; how much "slowness" that introduces I cannot say.
0
All Courses

From novice to tech pro — start learning today.