Solved

# Calculating age based on month

Posted on 2014-10-17
139 Views
If I am calculating age based on "date" using this query

``````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
``````

How can I calculate based on "month" only?  Thanks for helping this novice sql person.
0
Question by:al4629740
• 4
• 3
• 2

LVL 48

Expert Comment

please provide an example of what you mean by "month only"

is the DOB "month only"?
are you still comparing to the constant '20140701'?
an example would really help
0

Author Comment

No, the new constant will be the month

1
2
3
4
5
6
7
8
9
10
11
12
0

LVL 69

Expert Comment

If you want to see if a person is less than a certain age, you can just calculate the "controlling date", and then test for the DOB < that.  Something like this:

select *
from tblregistrations
WHERE
0

Author Comment

No, I just want to calculate their age based on current MONTH and DOB
0

LVL 69

Accepted Solution

ScottPletcher earned 500 total points
DATEDIFF(MONTH, DOB, GETDATE()) / 12
0

Author Comment

So I can basically stick whatever date I am comparing to in the GETDATE() section?

For example:

DATEDIFF(MONTH, DOB, GETDATE()) / 12
or
DATEDIFF(MONTH, DOB, '12/1/2014') / 12
or
DATEDIFF(MONTH, DOB, variable) / 12
0

LVL 48

Expert Comment

So "current month" can be treated as "1st of" the current month, and the calculation method remains the same.

``````declare @datum as date
set @datum = (select DATEADD(dd, - (DAY(getdate()) - 1), cast(getdate() as date) ) )

select *
from tblregistrations
WHERE  YEAR(@datum) - YEAR(DOB)
- ( CASE
WHEN MONTH(DOB) > MONTH(@datum)
OR ( MONTH(DOB) = MONTH(@datum)
AND DAY(DOB) > DAY(@datum)
)
THEN 1
ELSE 0
END )  < 18
``````
line 2 above calculates "1st day of current month"
0

LVL 69

Expert Comment

Yep, that should be it, if all you're worried about is the month of the dob.
0

Author Comment

Yes, it would be comparing the month of DOB to month of whatever I specify
0

## Featured Post

### Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.