Link to home
Create AccountLog in
Avatar of johnnyg123
johnnyg123Flag for United States of America

asked on

sql query to get age as of a given date

here is some sample data from a table named Customer

CustId   Birthdate
1            05/07/1970
2            05/07/1970
3           05/07/1970


here is some sample data from a table named OrderHistory

CustID    OrderDate        OrderAmount
1              05/07/2019      200
2              05/06/2019      300
3              05/08/2019      400



Trying to write a query that will return the age of the customer as of the order date

in example above, query should return

custid  orderdate           ageasoforderdate

1           05/07/2019       49
2           05/06/2019      48
3           05/08/2019       49
Avatar of Máté Farkas
Máté Farkas
Flag of Hungary image

SELECT oh.CustID, oh.OrderDate, DATEDIFF(year, c.Birthdate, oh.OrderDate) as ageasoforderdate
FROM OrderHistory oh
INNER JOIN Customer c on c.CustID = oh.CustID

Open in new window


for your scenario, try use this instead:

Select a.CustID, a.OrderDate,
Floor(DATEDIFF(DAY,b.Birthdate, a.OrderDate) / 365.2425) ageasoforderdate
from OrderHistory a
inner join Customer b on a.CustID = b.CustId

Open in new window

You may need to consider to handle the leap year as well.
Are we sure the person does not want to know that the person has aged 1 year since the last order?
Avatar of johnnyg123

ASKER

Hi Ryan

It does appear there does need to be considerations for leap year.  Works fine unless birthday is in february

Ideas?
Hi Arnold,

here is what I have so far

Select a.CustID, a.OrderDate,
case when month(getdate() ) = month(b.BIRTHDATE) and DAY(getdate() ) > =  DAY(b.BIRTHDATE)   then Floor(DATEDIFF(DAY,b.Birthdate, orderdate) / 365.2425) +1  else Floor(DATEDIFF(DAY,b.Birthdate, orderdate) / 365.2425)  end AS AgeOrderDate
from OrderHistory a
inner join Customer b on a.CustID = b.CustId
Can you actually post what is the output that you are expecting?
Both return the age the person was on the day of last order.

Both Mate's and Ryan's examples will do t
I do not understand why you are using case, what are you testing?

Datediff (year,b.Birthdate,a.orderdate) will return the age 
It does appear there does need to be considerations for leap year.  Works fine unless birthday is in february

Ideas?                                  
So, you need to tell us what if customer's DOB is on 29 Feb, how you wish to handle this?
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account