• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • Last Modified:

Calculating age based on month

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

Open in new window


How can I calculate based on "month" only?  Thanks for helping this novice sql person.
0
al4629740
Asked:
al4629740
  • 4
  • 3
  • 2
1 Solution
 
PortletPaulCommented:
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
 
al4629740Author Commented:
No, the new constant will be the month

1
2
3
4
5
6
7
8
9
10
11
12
0
 
Scott PletcherSenior DBACommented:
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  
    DOB < DATEADD(YEAR, -18, '20140701')
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
al4629740Author Commented:
No, I just want to calculate their age based on current MONTH and DOB
0
 
Scott PletcherSenior DBACommented:
DATEDIFF(MONTH, DOB, GETDATE()) / 12
0
 
al4629740Author Commented:
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
 
PortletPaulCommented:
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

Open in new window

line 2 above calculates "1st day of current month"
0
 
Scott PletcherSenior DBACommented:
Yep, that should be it, if all you're worried about is the month of the dob.
0
 
al4629740Author Commented:
Yes, it would be comparing the month of DOB to month of whatever I specify
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now