Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Age function (T-SQL 2000)

    vchuser3 is nvarchar 255 and currently stores a Date of Birth that needs to be converted to the current age of the individual held in the record.

    I used:

    <code>FLOOR(DATEDIFF(DAY, vchuser3, getdate()) / 365.25) as Age,</code>

    but get the error:

    <pre>Arithmetic overflow error converting expression to data type datetime.</pre>


    I am aware that this is because of the data types, how can I get this to calculate as DateTime

    I have tried <pre>FLOOR(DATEDIFF(DAY, CONVERT(DATETIME, vchuser3, 102),getdate()) / 365.25) as Age,</pre>

    to no avail
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Age function (T-SQL 2000)

    Does
    <code>
    FLOOR(DATEDIFF(DAY, CAST(vchuser3 AS datetime), getdate()) / 365.25) as Age,
    </code>
    work?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Age function (T-SQL 2000)

    <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    Nope, sorry.....
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Age function (T-SQL 2000)

    Have you tried to specify the date format, for example

    SET DATEFORMAT ymd

    Also: if the vchuser3 column contains NULL values, both the conversion and the age calculation will fail. You'd have to check for NULL values using a SELECT CASE ... END

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Age function (T-SQL 2000)

    Aaahhh just checked and there are NULLs in vchuser3...ggrrrrr

    I have done this but still get the overflow on both (note one is commented out)

    <pre>...

    CASE vchuser3
    WHEN null then 'No Data'
    else

    --FLOOR(DATEDIFF(DAY, vchuser3, getdate()) / 365.25)

    FLOOR(DATEDIFF(DAY, CAST(vchuser3 AS datetime), getdate()) / 365.25)
    end
    "Age",

    ...
    </pre>

    Jerry

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Age function (T-SQL 2000)

    Hmmm... have you tried using CONVERT with other constants than the 102 you originally used?

    Otherwise, I'm running out of ideas...

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Age function (T-SQL 2000)

    Was it that obvious <img src=/S/grin.gif border=0 alt=grin width=15 height=15>?

    <pre>FLOOR(DATEDIFF(DAY, CONVERT(DATETIME, vchuser3, 103),getdate()) / 365.25)</pre>


    I changed it to British/French value of 103 and I got it to run with the CASE...WHEN

    There was an error but I have a feeling that there are badly formatted dates in vchuser3, thanks for your patience.
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •