Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194

    Thumbs up Unanswered: average date of birth

    Hi, folks.
    I have a table with a column Date_of_birth.
    I want to evaluate average date_of_birth by grouping on depts.
    AVG function doesn't seem to work with datetime col. Plz help.

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Quote Originally Posted by thebeginner
    Hi, folks.
    I have a table with a column Date_of_birth.
    I want to evaluate average date_of_birth by grouping on depts.
    AVG function doesn't seem to work with datetime col. Plz help.
    r u looking for average of age ????
    coz avg of date of birth doesn't make any sense to me.

  3. #3
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    yes, i want to evaluate average age on the basis of date of brith stored with in the column
    Last edited by thebeginner; 07-08-04 at 09:45.

  4. #4
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96
    datediff ?

  5. #5
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    AVG(datediff(yy,date_of_birth,getdate()))
    this would work, i guess.
    thanx

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How's about:

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 datetime)
    GO
    
    INSERT INTO myTable99(Col1) 
    SELECT '10/24/1960' UNION ALL SELECT '10/24/1970' UNION ALL SELECT '10/24/1980'
    
    SELECT CONVERT(datetime,AVG(CONVERT(float,Col1))) FROM myTable99
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    Hi Brett, good morning.
    That was what i've been lookin for.
    How do we get the date back after it's converted into float.
    SELECT convert(datetime,38174.0)
    --------------------------
    2004-07-08 00:00:00.000
    (1 row(s) affected)




    Howdy!

  8. #8
    Join Date
    Jul 2004
    Location
    Irvine, CA
    Posts
    7

    Post

    Here is a quick one:





    ************************************************** ****
    SET NOCOUNT ON
    /* Here is your table */
    create table #working (Date_of_birth datetime)
    insert into #working (Date_of_birth) VALUES ('1/1/1920')
    insert into #working (Date_of_birth) VALUES ('1/6/1930')
    insert into #working (Date_of_birth) VALUES ('1/17/1940')
    DECLARE @days_old int

    /* Average days old */
    SELECT @days_old = AVG(datediff(dd, Date_of_birth, GETDATE()))
    FROM #working

    /* back it out now for average birth date, odd as that is :P */
    SELECT dateadd(dd, -@days_old, GETDATE())

    drop table #working
    SET NOCOUNT OFF
    ************************************************** ****

    Hope it helps

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Aren't you concerned that cobalt sound a lot like COBOL?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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