Solved

Calculating age based on month

Posted on 2014-10-17
9
152 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL date incremented 11 30
string fuctions 4 26
Sql server, import complete table, using vb.net 9 34
sql server service accounts 4 23
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 …
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

776 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