December 1, 2017 at 9:21 am
Select
Datediff(yy, '20161201', '20171130') as age1 ,
FLOOR(DATEDIFF(DAY, '20161201', '20171201') / 365.25) as age2
So the baby was born on 20161201
Now, I want his age t appear 1 on 12-01-2017
What SQl statement should I use ?
if you run the above code, the output is wrong.
1. The bay was exactly 1 on his birthday 12-01-2017 - but it says 0
2. if you change the date in the FLOOR(.... to 20171202 you get 1 .. Ahhh but the baby should be one exactly on his birthday which is 20171201 !
December 1, 2017 at 9:22 am
December 1, 2017 at 9:57 am
Lynn Wins!
Your formula even works for leap years.....
Have Fun
declare @dob datetime,
@age int,
@day datetime
set @dob = '2016-12-01'
set @day = '2017-11-30'
set @age = datediff(yy,@dob,@day) -
case when @day < dateadd(yy,datediff(yy,@dob,@day), @dob) then 1 else 0 end
select @age
--BABY IS ZERO because we calculated his age one day before his birthday -- PERFECT
Example 2
BABY TURNED ONE .. even when she was born on a leap year ..
declare @dob datetime,
@age int,
@day datetime
set @dob = '2016-02-29'
set @day = '2017-02-28'
set @age = datediff(yy,@dob,@day) -
case when @day < dateadd(yy,datediff(yy,@dob,@day), @dob) then 1 else 0 end
select @age
Conclusion:
LYNN WINS!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply