Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Join Date
    Aug 2004
    Posts
    9

    Unanswered: sql date calcualtion from dob

    hi all;

    I have seen posts on numerous websites to get the age from a dob column I am faced with the same problem but get this error:

    Server: Msg 403, Level 16, State 1, Line 1
    Invalid operator for data type. Operator equals divide, type equals datetime.


    when I run the following query:

    SELECT ROUND((currentdate - DOB)/365.24,0) FROM FL1_A_Backup

    All I need is a simple statement that calcualtes the age from the dob column(smalldatetime) then creates a new row which it puts the data into.
    I have already created a CurrentDate column which has a (getdate()) value attached to it.

    This is nothing fancy or complicated and I just need to figure out from the year I'm not really concerned about if the person has a bday tomorrow or so on.

    I know I am probably doing something wrong with this query and am not shy to say that I have only been using ms sql for a couple of months (basic select statements).

  2. #2
    Join Date
    Jul 2004
    Location
    Mars
    Posts
    137

    Thumbs up This should work

    Hai...

    Try this one..this example gives my age correctly..


    SELECT datediff(year,'feb 3 1983',getdate())

    I have hardcoded the date into the function. In your case u have to change it with ur column name.
    Try it and tell me whether it works or not..

    with regards
    Sudar

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select year(getdate()) 
         - year(DOB) 
         - case when month(DOB) > month(getdate()) then 1
                when month(DOB) < month(getdate()) then 0
                when day(DOB) > day(DOB) then 1
                else 0
            end as age
      from FL1_A_Backup
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2004
    Posts
    9

    awesome

    great the procedure worked perfectly; now how would; using the alter table command I insert this into my table

  5. #5
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    A nice and elegant solution can be found here:

    http://weblogs.sqlteam.com/leed/arch...8/18/1933.aspx
    Davide Mauri
    http://www.davidemauri.it

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    davide, the problem with dividing by 365.25 is that you will get the wrong answer for certain birthdates

    i had a discussion with someone about this, and my opinion is that "works correctly 99% of the time" is not acceptable where computers are concerned (you would certainly not be happy if your paycheque is not always 100% accurate, like, say, if it dropped the leading digit once in a while)

    reiqwan, you do not want to store the result, just display it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2004
    Posts
    9

    thats it

    thanks for the help

  8. #8
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    rq97 you're surely right, 99% is not acceptable for computers being.

    Can you made some examples of "wrong" values? I tried some, but everything works fine.
    Davide Mauri
    http://www.davidemauri.it

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    birthday on feb 28 and/or feb 29
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    I've grabbed the source from the link posted above and made 2 simple changed to test against the date you said:

    Code:
    declare @birthday datetime 
    set @birthday = '1968-02-29' 
    
    declare @today datetime
    set @today = '2004-02-28'
    
    -- int
    SELECT floor(CAST(DATEDIFF(dd, @birthday, @today) AS decimal(18, 7)) / 365.25) 
    
    --or return it as a decimal 
    SELECT CAST(DATEDIFF(dd, @birthday, @today) AS decimal(18, 7)) / 365.25
    i've found no problem testing with dates like 28 feb and 29 feb. I've made a lot of attempts (from 1968 to 2208) and all went fine.

    Any ideas?
    Davide Mauri
    http://www.davidemauri.it

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    set @birthday = '1985-02-28'
    set @today = '2004-02-28'

    it says i'm 18 but it's wrong

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80

    Thumbs up

    yes, you're right.

    that's because a year is *not* made of 365.25 days, but of 365. The .25 is just an aproximation for the 366-days year......mmmm...nice challenge :-) i'll try to find a way to solve it using math only. it should be possibile.

    Btw your solution posted above is perfect, very elegant!
    Last edited by manowar; 08-26-04 at 12:36.
    Davide Mauri
    http://www.davidemauri.it

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by manowar
    your solution posted above is perfect, very elegant!
    thank you, thank you very much

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by manowar
    i'll try to find a way to solve it using math only. it should be possibile.

    Btw your solution posted above is perfect, very elegant!
    You might try the "gregorian approximation" of 365.2425 (this accounts for the leap years as observed by the current Gregorian calendar).

    Yes, Rudy's answer works... Every time! A mathematical answer would be interesting, but I'd still use the one that Rudy proposed just because it can be proved to be logically correct.

    -PatP

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks for the kind words, pat

    if i seem disoriented it's because there were two simultaneous threads on the vary same subject (which only comes up every few months)

    the other one is over there
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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