Can someone tell me why the code below returns 5.0 instead of 5.2 please?
declare @age decimal(10,1)
declare @dob datetime
set @dob = '2015-07-31 00:00:00.000'
set @age = datediff(m,@dob,getDate()) / 12;
print @age
try "/ 12.0"
October 14, 2020 at 12:14 am
Perfect thanks.
And now I have that sorted I realise there is a fundamental flaw in my logic.
What I'm trying to do here is get the years/months returned as 3.3 for example. And while the example I posted works well the snippet below returns 0.9 instead of 0.11 (11 months). Obviously it is not as simple as /12
declare @age decimal(10,1)
declare @dob datetime
set @dob = '2019-11-01 00:00:00.000'
set @age = datediff(m,@dob,getDate()) / 12.0;
print @age
October 14, 2020 at 3:18 am
Look up for my article "age calculation" on this web site.
All is done for you. 🙂
_____________
Code for TallyGenerator
October 14, 2020 at 9:02 am
Assuming you consider only whole months, truncating any remaining days, then:
declare @age varchar(6)
declare @dob datetime
set @dob = '2015-07-31 00:00:00.000'
select @age = cast(months_old / 12 as varchar(3)) + '.' + cast(months_old % 12 as varchar(2))
from (
select datediff(month,@dob,getDate()) - case when day(getdate()) < day(@dob) then 1 else 0 end as months_old
) as calc1
print @age
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 14, 2020 at 11:03 pm
Awesome thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply