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
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
Microsoft SQL ServerSQL
Last Comment
Scott Pletcher
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
Are we sure the person does not want to know that the person has aged 1 year since the last order?
ASKER
Hi Ryan
It does appear there does need to be considerations for leap year. Works fine unless birthday is in february
Ideas?
It does appear there does need to be considerations for leap year. Works fine unless birthday is in february
Ideas?
ASKER
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
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
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 februarySo, you need to tell us what if customer's DOB is on 29 Feb, how you wish to handle this?
Ideas?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Open in new window