Solved

Calculating age based on month

Posted on 2014-10-17
9
158 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
  • 4
  • 3
  • 2
9 Comments
 
LVL 48

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 69

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

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

Accepted Solution

by:
Scott Pletcher earned 500 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 48

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 69

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

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 …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

756 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