Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Calculating age based on month

Posted on 2014-10-17
9
Medium Priority
?
162 Views
Last Modified: 2014-10-17
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
Comment
Question by:al4629740
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40387609
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

by:al4629740
ID: 40387621
No, the new constant will be the month

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

Expert Comment

by:Scott Pletcher
ID: 40387631
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
Independent Software Vendors: 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!

 

Author Comment

by:al4629740
ID: 40387644
No, I just want to calculate their age based on current MONTH and DOB
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40387647
DATEDIFF(MONTH, DOB, GETDATE()) / 12
0
 

Author Comment

by:al4629740
ID: 40387650
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 49

Expert Comment

by:PortletPaul
ID: 40387653
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40387655
Yep, that should be it, if all you're worried about is the month of the dob.
0
 

Author Comment

by:al4629740
ID: 40387659
Yes, it would be comparing the month of DOB to month of whatever I specify
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question