Age as of January 1st

• thomashohner

  SSCrazy Eights

  Points: 9856

  More actions

  January 27, 2017 at 2:22 pm

  #334615

  Hello all,
  I'm really strugling on this age calcualtion. I need to find the age of a patient based off January 1st of the AsofDate.
  So if I have a BirthDate = 11/30/1951 there age as a whole number as of January 1st of the service year. In this case any date within 2016
  So  any date in 1951 should = 65 
  any date in 1952 = 63
  any date in 1992 = 22

  ect.
  Were born between January 1, 1952, and December 31, 1992 is the same as 23-64

  I have been using the following code below but keep getting the wrong answer. Any help would be appreciated. I even thought about a age table simple based on year. But would like a calculation if possible.
  DECLARE @Birth DATETIME = '11/30/1951'
   ,@Service DATETIME = '2016-08-26'

  SELECT PeriodAge  = DATEDIFF(YEAR,@Birth, '01/01/'+CONVERT( VARCHAR, DATEPART(YEAR, @Service)))
            -CASE
                  WHEN DATEADD(DD, DATEDIFF(DD, 0, '01/01/'+CONVERT(VARCHAR, DATEPART(YEAR, @Service))), 0)
                        < DATEADD(YEAR, DATEDIFF(YEAR,@Birth, '01/01/'+CONVERT(VARCHAR, DATEPART(YEAR, @Service))),@Birth)
                         THEN 1
                   ELSE 0
             END

  ***SQL born on date Spring 2013:-)

• thomashohner

  SSCrazy Eights

  Points: 9856

  More actions

  January 27, 2017 at 2:32 pm

  #1925441

  thomashohner - Friday, January 27, 2017 2:22 PM

  Hello all,
  I'm really strugling on this age calcualtion. I need to find the age of a patient based off January 1st of the AsofDate.
  So if I have a BirthDate = 11/30/1951 there age as a whole number as of January 1st of the service year. In this case any date within 2016
  So  any date in 1951 should = 65 
  any date in 1952 = 63
  any date in 1992 = 22

  ect.
  Were born between January 1, 1952, and December 31, 1992 is the same as 23-64

  I have been using the following code below but keep getting the wrong answer. Any help would be appreciated. I even thought about a age table simple based on year. But would like a calculation if possible.
  DECLARE @Birth DATETIME = '11/30/1951'
   ,@Service DATETIME = '2016-08-26'

  SELECT PeriodAge  = DATEDIFF(YEAR,@Birth, '01/01/'+CONVERT( VARCHAR, DATEPART(YEAR, @Service)))
            -CASE
                  WHEN DATEADD(DD, DATEDIFF(DD, 0, '01/01/'+CONVERT(VARCHAR, DATEPART(YEAR, @Service))), 0)
                        < DATEADD(YEAR, DATEDIFF(YEAR,@Birth, '01/01/'+CONVERT(VARCHAR, DATEPART(YEAR, @Service))),@Birth)
                         THEN 1
                   ELSE 0
             END

  Any date in 1992 should be 23 not 22

  ***SQL born on date Spring 2013:-)

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  January 27, 2017 at 2:56 pm

  #1925444

  thomashohner - Friday, January 27, 2017 2:32 PM

  thomashohner - Friday, January 27, 2017 2:22 PM

  Hello all,
  I'm really strugling on this age calcualtion. I need to find the age of a patient based off January 1st of the AsofDate.
  So if I have a BirthDate = 11/30/1951 there age as a whole number as of January 1st of the service year. In this case any date within 2016
  So  any date in 1951 should = 65 
  any date in 1952 = 63
  any date in 1992 = 22

  ect.
  Were born between January 1, 1952, and December 31, 1992 is the same as 23-64

  I have been using the following code below but keep getting the wrong answer. Any help would be appreciated. I even thought about a age table simple based on year. But would like a calculation if possible.
  DECLARE @Birth DATETIME = '11/30/1951'
   ,@Service DATETIME = '2016-08-26'

  SELECT PeriodAge  = DATEDIFF(YEAR,@Birth, '01/01/'+CONVERT( VARCHAR, DATEPART(YEAR, @Service)))
            -CASE
                  WHEN DATEADD(DD, DATEDIFF(DD, 0, '01/01/'+CONVERT(VARCHAR, DATEPART(YEAR, @Service))), 0)
                        < DATEADD(YEAR, DATEDIFF(YEAR,@Birth, '01/01/'+CONVERT(VARCHAR, DATEPART(YEAR, @Service))),@Birth)
                         THEN 1
                   ELSE 0
             END

  Any date in 1992 should be 23 not 22

  Try:
  SELECT DateDiff(yy, '19560714', DateAdd(yy, DateDiff(yy, 0, GetDate()),0))-1 Age;

• Thom A

  SSC Guru

  Points: 99130

  More actions

  January 27, 2017 at 2:56 pm

  #1925446

  Does this work?

  DECLARE @Birthday DATE,
   @AsOf Date;
SET @Birthday = '01-May-1987';
SET @AsOf = '19-May-2000';

  SELECT DATEDIFF(YEAR, @Birthday, DATEADD(YEAR, DATEDIFF(YEAR, 0, @AsOf),0)) -
   CASE WHEN DATEPART(MONTH, @Birthday) = 1 AND DATEPART(DAY, @Birthday) = 1 THEN 0 ELSE 1 END AS AgeAtYearStart;

  I've made it so that IF the person was born on the first then don't minus the year.

  I was confused by your logic about someone in '51 being 65. They would be 64 at the start of that year. They would turn 65 in that year. If you don't want that logic though, then simply this would work:
  DECLARE @Birthday DATE,
   @AsOf Date;
SET @Birthday = '30-Nov-1951';
SET @AsOf = '19-May-2016';

  SELECT DATEDIFF(YEAR, @Birthday, @AsOf) AS AgeAtYearStart;

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• thomashohner

  SSCrazy Eights

  Points: 9856

  More actions

  January 27, 2017 at 3:03 pm

  #1925447

  I appreciate the help. I'm goingto try yours right now.  Its logic CMS uses and it threw me for a loop. It use to be different but now that they changed it it broke my stuff and it does not make alot of sense. So for example the date range on Dental is 6-9 year olds but those are the dates they give and my calculation never lines up now.

  ***SQL born on date Spring 2013:-)

• thomashohner

  SSCrazy Eights

  Points: 9856

  More actions

  January 27, 2017 at 3:39 pm

  #1925451

  Unfortunately that did not work. Example '01/01/1993' Should be 22 not 23. Both of our code makes it 23. However change it to '01/02/1993' and it does correctly read as 22.

  ***SQL born on date Spring 2013:-)

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  January 27, 2017 at 4:05 pm

  #1925453

  thomashohner - Friday, January 27, 2017 3:39 PM

  Unfortunately that did not work. Example '01/01/1993' Should be 22 not 23. Both of our code makes it 23. However change it to '01/02/1993' and it does correctly read as 22.

  SELECT DateDiff(yy, '01/01/1993', DateAdd(dd,-1,DateAdd(yy, DateDiff(yy, 0, GetDate()),0)))-1 Age;

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  January 27, 2017 at 4:13 pm

  #1925454

  Joe Torre - Friday, January 27, 2017 4:05 PM

  thomashohner - Friday, January 27, 2017 3:39 PM

  Unfortunately that did not work. Example '01/01/1993' Should be 22 not 23. Both of our code makes it 23. However change it to '01/02/1993' and it does correctly read as 22.

  SELECT DateDiff(yy, '01/01/1993', DateAdd(dd,-1,DateAdd(yy, DateDiff(yy, 0, GetDate()),0)))-1 Age;

  CREATE FUNCTION AgeAsOf0101 (@DOB date)

  RETURNS tinyint

  AS

  BEGIN

  DECLARE @ret tinyint;

  SELECT

  @ret = DateDiff(yy, '01/01/1993', DateAdd(dd, -1, DateAdd(yy, DateDiff(yy, 0, GetDate()), 0))) - 1;

  RETURN @ret;

  END;

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  January 27, 2017 at 4:21 pm

  #1925455

  Joe Torre - Friday, January 27, 2017 4:13 PM

  Joe Torre - Friday, January 27, 2017 4:05 PM

  thomashohner - Friday, January 27, 2017 3:39 PM

  Unfortunately that did not work. Example '01/01/1993' Should be 22 not 23. Both of our code makes it 23. However change it to '01/02/1993' and it does correctly read as 22.

  SELECT DateDiff(yy, '01/01/1993', DateAdd(dd,-1,DateAdd(yy, DateDiff(yy, 0, GetDate()),0)))-1 Age;

  CREATE FUNCTION AgeAsOf0101 (@DOB date)

  RETURNS tinyint

  AS

  BEGIN

  DECLARE @ret tinyint;

  SELECT

  @ret = DateDiff(yy, '01/01/1993', DateAdd(dd, -1, DateAdd(yy, DateDiff(yy, 0, GetDate()), 0))) - 1;

  RETURN @ret;

  END;

  IF Object_Id(N'dbo.AgeAsOf0101', 'FN') IS NOT NULL DROP FUNCTION AgeAsOf0101;

  go

  CREATE FUNCTION dbo.AgeAsOf0101 (@DOB date)

  RETURNS tinyint

  AS

  BEGIN

  DECLARE @ret tinyint;

  SELECT

  @ret = DateDiff(yy, '01/01/1993', DateAdd(dd, -1, DateAdd(yy, DateDiff(yy, 0, GetDate()), 0))) - 1;

  RETURN @ret;

  END;

  GO

  SELECT dbo.AgeAsOf0101('19920101') Age;

  
  

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  January 27, 2017 at 5:25 pm

  #1925459

  Isn't this simply a question of integer math?
  😎

  DECLARE @Birth DATETIME  = CONVERT(DATETIME,'19921130',112); 
DECLARE @Service DATETIME = CONVERT(DATETIME,'20160826',112);

  SELECT (DATEDIFF(MONTH,0,@Service) - DATEDIFF(MONTH,0,@Birth)) / 12 AS AgeAsOfJanFirst;

  Output
  AgeAsOfJanFirst
---------------
23


• thomashohner

  SSCrazy Eights

  Points: 9856

  More actions

  January 27, 2017 at 6:11 pm

  #1925462

  Erikur, your amazing i feel like such an idiot. So far each test i have done your simple calc has provided the correct result. Thanks you Sir !!

  ***SQL born on date Spring 2013:-)

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  January 28, 2017 at 12:17 am

  #1925469

  You are very welcome Thomas, always happy to help.
  😎

  Here is another method which is equally simple
  DATEDIFF(YEAR,@Birth,DATEFROMPARTS(YEAR(@Service),1,1))

  Recommend that you set up a simple test harness to assess and compare to the business requirements, here is a simple example
  

  USE TEEST;GOSET NOCOUNT ON;DECLARE @TESTDATE TABLE 
(
    TD_ID               INT IDENTITY(1,1)   NOT NULL PRIMARY KEY CLUSTERED
   ,DOB                 DATE                NOT NULL
   ,DOS                 DATE                NOT NULL
   ,AgeAsOfJanFirst     INT                 NOT NULL
   ,DFPAgeAsOfJanFirst  INT                 NOT NULL
   ,ActualAge           INT                 NOT NULL);

  DECLARE @Birth DATETIME   = CONVERT(DATETIME,'19921231',112); 
DECLARE @Service DATETIME = CONVERT(DATETIME,'20170128',112);INSERT INTO @TESTDATE(DOB,DOS,AgeAsOfJanFirst,DFPAgeAsOfJanFirst,ActualAge)SELECT 
    CONVERT(DATE,@Birth  ,0) AS DOB
   ,CONVERT(DATE,@Service,0) AS DOS
   ,(DATEDIFF(MONTH,0,@Service) - DATEDIFF(MONTH,0,@Birth)) / 12 AS AgeAsOfJanFirst
   ,DATEDIFF(YEAR,@Birth,DATEFROMPARTS(YEAR(@Service),1,1))     AS DFPAgeAsOfJanFirst
   ,DATEDIFF(YEAR,@Birth,@Service) AS ActualAge;

  SET @Service    =   CONVERT(DATETIME,'20170101',112); 
SET @Birth      =   CONVERT(DATETIME,'20001231',112); 
INSERT INTO @TESTDATE(DOB,DOS,AgeAsOfJanFirst,DFPAgeAsOfJanFirst,ActualAge)SELECT 
    CONVERT(DATE,@Birth  ,0) AS DOB
   ,CONVERT(DATE,@Service,0) AS DOS
   ,(DATEDIFF(MONTH,0,@Service) - DATEDIFF(MONTH,0,@Birth)) / 12 AS AgeAsOfJanFirst
   ,DATEDIFF(YEAR,@Birth,DATEFROMPARTS(YEAR(@Service),1,1))     AS DFPAgeAsOfJanFirst
   ,DATEDIFF(YEAR,@Birth,@Service) AS ActualAge;

  SET @Service    =   CONVERT(DATETIME,'20170101',112); 
SET @Birth      =   CONVERT(DATETIME,'20010101',112); 
INSERT INTO @TESTDATE(DOB,DOS,AgeAsOfJanFirst,DFPAgeAsOfJanFirst,ActualAge)SELECT 
    CONVERT(DATE,@Birth  ,0) AS DOB
   ,CONVERT(DATE,@Service,0) AS DOS
   ,(DATEDIFF(MONTH,0,@Service) - DATEDIFF(MONTH,0,@Birth)) / 12 AS AgeAsOfJanFirst
   ,DATEDIFF(YEAR,@Birth,DATEFROMPARTS(YEAR(@Service),1,1))     AS DFPAgeAsOfJanFirst
   ,DATEDIFF(YEAR,@Birth,@Service) AS ActualAge;

  SET @Service    =   CONVERT(DATETIME,'20170101',112); 
SET @Birth      =   CONVERT(DATETIME,'20010102',112); 
INSERT INTO @TESTDATE(DOB,DOS,AgeAsOfJanFirst,DFPAgeAsOfJanFirst,ActualAge)SELECT 
    CONVERT(DATE,@Birth  ,0) AS DOB
   ,CONVERT(DATE,@Service,0) AS DOS
   ,(DATEDIFF(MONTH,0,@Service) - DATEDIFF(MONTH,0,@Birth)) / 12 AS AgeAsOfJanFirst
   ,DATEDIFF(YEAR,@Birth,DATEFROMPARTS(YEAR(@Service),1,1))     AS DFPAgeAsOfJanFirst
   ,DATEDIFF(YEAR,@Birth,@Service) AS ActualAge;

  SET @Service    =   CONVERT(DATETIME,'20161231',112); 
SET @Birth      =   CONVERT(DATETIME,'20001231',112); 
INSERT INTO @TESTDATE(DOB,DOS,AgeAsOfJanFirst,DFPAgeAsOfJanFirst,ActualAge)SELECT 
    CONVERT(DATE,@Birth  ,0) AS DOB
   ,CONVERT(DATE,@Service,0) AS DOS
   ,(DATEDIFF(MONTH,0,@Service) - DATEDIFF(MONTH,0,@Birth)) / 12 AS AgeAsOfJanFirst
   ,DATEDIFF(YEAR,@Birth,DATEFROMPARTS(YEAR(@Service),1,1))     AS DFPAgeAsOfJanFirst
   ,DATEDIFF(YEAR,@Birth,@Service) AS ActualAge;

  SET @Service    =   CONVERT(DATETIME,'20170102',112); 
SET @Birth      =   CONVERT(DATETIME,'20001231',112); 
INSERT INTO @TESTDATE(DOB,DOS,AgeAsOfJanFirst,DFPAgeAsOfJanFirst,ActualAge)SELECT 
    CONVERT(DATE,@Birth  ,0) AS DOB
   ,CONVERT(DATE,@Service,0) AS DOS
   ,(DATEDIFF(MONTH,0,@Service) - DATEDIFF(MONTH,0,@Birth)) / 12 AS AgeAsOfJanFirst
   ,DATEDIFF(YEAR,@Birth,DATEFROMPARTS(YEAR(@Service),1,1))     AS DFPAgeAsOfJanFirst
   ,DATEDIFF(YEAR,@Birth,@Service) AS ActualAge;

  SET @Service    =   CONVERT(DATETIME,'20170103',112); 
SET @Birth      =   CONVERT(DATETIME,'20001231',112); 
INSERT INTO @TESTDATE(DOB,DOS,AgeAsOfJanFirst,DFPAgeAsOfJanFirst,ActualAge)SELECT 
    CONVERT(DATE,@Birth  ,0) AS DOB
   ,CONVERT(DATE,@Service,0) AS DOS
   ,(DATEDIFF(MONTH,0,@Service) - DATEDIFF(MONTH,0,@Birth)) / 12 AS AgeAsOfJanFirst
   ,DATEDIFF(YEAR,@Birth,DATEFROMPARTS(YEAR(@Service),1,1))     AS DFPAgeAsOfJanFirst
   ,DATEDIFF(YEAR,@Birth,@Service) AS ActualAge;

  SELECT
   T.DOB
  ,T.DOS
  ,T.AgeAsOfJanFirst
  ,T.DFPAgeAsOfJanFirst
  ,T.ActualAge
FROM    @TESTDATE   T

• thomashohner

  SSCrazy Eights

  Points: 9856

  More actions

  January 28, 2017 at 8:02 am

  #1925482

  Eirikur Eiriksson - Saturday, January 28, 2017 12:17 AM

  You are very welcome Thomas, always happy to help.
  😎

  Here is another method which is equally simple
  DATEDIFF(YEAR,@Birth,DATEFROMPARTS(YEAR(@Service),1,1))

  Recommend that you set up a simple test harness to assess and compare to the business requirements, here is a simple example
  

  USE TEEST;GOSET NOCOUNT ON;DECLARE @TESTDATE TABLE 
  (
      TD_ID               INT IDENTITY(1,1)   NOT NULL PRIMARY KEY CLUSTERED
     ,DOB                 DATE                NOT NULL
     ,DOS                 DATE                NOT NULL
     ,AgeAsOfJanFirst     INT                 NOT NULL
     ,DFPAgeAsOfJanFirst  INT                 NOT NULL
     ,ActualAge           INT                 NOT NULL);

  DECLARE @Birth DATETIME   = CONVERT(DATETIME,'19921231',112); 
  DECLARE @Service DATETIME = CONVERT(DATETIME,'20170128',112);INSERT INTO @TESTDATE(DOB,DOS,AgeAsOfJanFirst,DFPAgeAsOfJanFirst,ActualAge)SELECT 
      CONVERT(DATE,@Birth  ,0) AS DOB
     ,CONVERT(DATE,@Service,0) AS DOS
     ,(DATEDIFF(MONTH,0,@Service) - DATEDIFF(MONTH,0,@Birth)) / 12 AS AgeAsOfJanFirst
     ,DATEDIFF(YEAR,@Birth,DATEFROMPARTS(YEAR(@Service),1,1))     AS DFPAgeAsOfJanFirst
     ,DATEDIFF(YEAR,@Birth,@Service) AS ActualAge;

  SET @Service    =   CONVERT(DATETIME,'20170101',112); 
  SET @Birth      =   CONVERT(DATETIME,'20001231',112); 
  INSERT INTO @TESTDATE(DOB,DOS,AgeAsOfJanFirst,DFPAgeAsOfJanFirst,ActualAge)SELECT 
      CONVERT(DATE,@Birth  ,0) AS DOB
     ,CONVERT(DATE,@Service,0) AS DOS
     ,(DATEDIFF(MONTH,0,@Service) - DATEDIFF(MONTH,0,@Birth)) / 12 AS AgeAsOfJanFirst
     ,DATEDIFF(YEAR,@Birth,DATEFROMPARTS(YEAR(@Service),1,1))     AS DFPAgeAsOfJanFirst
     ,DATEDIFF(YEAR,@Birth,@Service) AS ActualAge;

  SET @Service    =   CONVERT(DATETIME,'20170101',112); 
  SET @Birth      =   CONVERT(DATETIME,'20010101',112); 
  INSERT INTO @TESTDATE(DOB,DOS,AgeAsOfJanFirst,DFPAgeAsOfJanFirst,ActualAge)SELECT 
      CONVERT(DATE,@Birth  ,0) AS DOB
     ,CONVERT(DATE,@Service,0) AS DOS
     ,(DATEDIFF(MONTH,0,@Service) - DATEDIFF(MONTH,0,@Birth)) / 12 AS AgeAsOfJanFirst
     ,DATEDIFF(YEAR,@Birth,DATEFROMPARTS(YEAR(@Service),1,1))     AS DFPAgeAsOfJanFirst
     ,DATEDIFF(YEAR,@Birth,@Service) AS ActualAge;

  SET @Service    =   CONVERT(DATETIME,'20170101',112); 
  SET @Birth      =   CONVERT(DATETIME,'20010102',112); 
  INSERT INTO @TESTDATE(DOB,DOS,AgeAsOfJanFirst,DFPAgeAsOfJanFirst,ActualAge)SELECT 
      CONVERT(DATE,@Birth  ,0) AS DOB
     ,CONVERT(DATE,@Service,0) AS DOS
     ,(DATEDIFF(MONTH,0,@Service) - 

  ***SQL born on date Spring 2013:-)

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  January 28, 2017 at 8:32 am

  #1925485

  My bad with the DATEFROMPARTS method, here is a correct version with the integer math
  😎
  ,DATEDIFF(MONTH,@Birth,DATEFROMPARTS(YEAR(@Service),1,1)) / 12 AS DFPAgeAsOfJanFirst

  
  Again and as always, you are welcome Thomas. 

• thomashohner

  SSCrazy Eights

  Points: 9856

  More actions

  January 28, 2017 at 9:33 am

  #1925487

  Eirikur Eiriksson - Saturday, January 28, 2017 8:32 AM

  My bad with the DATEFROMPARTS method, here is a correct version with the integer math
  😎
  ,DATEDIFF(MONTH,@Birth,DATEFROMPARTS(YEAR(@Service),1,1)) / 12 AS DFPAgeAsOfJanFirst

  
  Again and as always, you are welcome Thomas. 

  Awesome ! Thanks. This is processing on around 20 million rows so i did some tests. My old method not only was wrong with the new rules but much slower. The winner on speed was 
  DATEDIFF(MONTH,@Birth,DATEFROMPARTS(YEAR(@Service),1,1)) /12 even though the Month Method was very close after running several times DATEFROMPARTS was fastest. They all kept the same number of logical reads though.

  ***SQL born on date Spring 2013:-)