Losing my decimal bits

  • 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"

  • 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

    • This reply was modified 4 years ago by  Jay@Work.
  • Look up for my article "age calculation" on this web site.

    All is done for you. 🙂

    _____________
    Code for TallyGenerator

  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Awesome thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply