locked
Finding the Age RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello Friends,

    I am working on a query  to pull the records from a table. I need your inputs to include the correct condition in the where clause. There are 2 concerned fields - DateofService and DateofBirth. I am trying to pull the records only for the patients who were equal to or above 65 years old during the DateofService. I am trying the below SQL 

    declare @dob datetime,@dos datetime
set @dob = dateadd(yyyy,-65,getdate())
set @dos = '2012/05/01'
select datediff(mm,@dob,@dos)/12

    I tried to test it using variables. However, this shows the value as 65 even though the age would be exactly 65 only on 2012/05/03. I know that this is because of the month. Is there a way to check if the patient's age was exactly 65.

    Murali Krishnan

    Thursday, May 3, 2012 9:07 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Murli

    You can try following SQL to calculate Age.

    declare @dob datetime,@dos datetime
set @dob = dateadd(yyyy,-65,getdate())
set @dos = '2012/05/01'
SELECT YEAR(@dos) - YEAR(@dob) +
  CASE WHEN DATEADD(year,YEAR(@dos) - YEAR(@dob)
  , @dob) > @dos THEN -1 ELSE 0 END AS age

    Regards,

    Dipesh Mehta

    • Marked as answer by Murali_CHN Thursday, May 3, 2012 10:48 AM
    Thursday, May 3, 2012 10:28 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    Can you provide some sample data...May be , try using dateofservice instead of getdate() for determining age

    Thanks and regards, Rishabh K

    Thursday, May 3, 2012 9:21 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Can you provide some sample data...May be , try using dateofservice instead of getdate() for determining age

    Thanks and regards, Rishabh K


    Rishabh.. I am trying to calculate the age of the patients during thier dateofservice....I do not have any sample data though...As i have mentioned, one column would be having the patient's DOS and another column would be having patient's DOB and i would like to pull the records where the patient's age was exactly 65 and above during the DOS

    Murali Krishnan

    Thursday, May 3, 2012 9:31 AM
  • Question
    Sign in to vote
    1
    Sign in to vote

    does this help???

    create table patient(name varchar(50),dos date)

    insert into patient
    values('cde','1954-05-03')

    insert into patient
    values('abc','1947-05-03')

    select * from patient
    where DATEdiff(yyyy,dos,getdate())>65

    or this

    select* from patient
    where DATEADD(yyyy,-65,GETDATE()) >=dos


    Thursday, May 3, 2012 9:37 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Murali,

    Here is your solution...

    declare @dob datetime,@dos DATETIME;
    set @dob = '1947/05/03';
    --set @dob = dateadd(yyyy,-65,getdate());
    --PRINT  @dob
    set @dos = '2012/05/01'
    print datediff(dd,@dob,@dos)
    PRINT convert(FLOAT,datediff(dd,@dob,@dos)/365.0)

    ------------------------------------------------------------------------------------------------------------------------------------------------------------

    Please mark it as answer if it helps you.so other people can use it as reference...

    Thanks!


    • Proposed as answer by Anal Patel Thursday, May 3, 2012 9:46 AM
    • Edited by Anal Patel Thursday, May 3, 2012 9:54 AM
    Thursday, May 3, 2012 9:46 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Murli

    You can try following SQL to calculate Age.

    declare @dob datetime,@dos datetime
set @dob = dateadd(yyyy,-65,getdate())
set @dos = '2012/05/01'
SELECT YEAR(@dos) - YEAR(@dob) +
  CASE WHEN DATEADD(year,YEAR(@dos) - YEAR(@dob)
  , @dob) > @dos THEN -1 ELSE 0 END AS age

    Regards,

    Dipesh Mehta

    • Marked as answer by Murali_CHN Thursday, May 3, 2012 10:48 AM
    Thursday, May 3, 2012 10:28 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    may this help :

    DECLARE @BirthDate DATETIME,
@ServiceDate DATETIME,
@Year INT

SELECT @BirthDate = '10-16-1982',
		@ServiceDate = '10-16-2012'

SELECT @Year = DATEDIFF(yy,@BirthDate,@ServiceDate)
select @Year

    Please vote if you find this posting was helpful or Mark it as answered.

    Thursday, May 3, 2012 10:44 AM