Post reply

Age as of January 1st

  • 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:-)

  • 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:-)

  • 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;

  • 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

  • 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:-)

  • 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:-)

  • 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;

  • 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;

  • 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;


    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAHIAAABVCAYAAACVdLDzAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAAAqVSURBVHhe7Zx/bFPXFce/zqRkfyBg2iYG0rop2CwkgUpOIjVGGtJEkIFAna2kHVX5IYRN2aY6VckkNpgoEhpJJxwqhdhVAYG6lXRTHJFfEKhEJZKtJCmQBJLYYajjpxBVoCAtGfB2z333PWzHduw4P+yX95Fu3r3n3WfZ7/vOOfde39jw9OlTCRrh/L/8yFn4A9GaWaSJo06KM2OF/Po/d0RNG6RJUgu2p6cjnZftaJFGR9pIfSS/C4URrklmtCYikXb67XWQ6ocxMjKC3gOXse7t0+JUAKe9OLKlnvep33IEe6sGZfMH5VhafxhWg4G3UwEtikikeY9sgc0qN4zFpTBf7oM/1MNY+7mogtUk1iZv3Hv5AN4T16YCWhWRSJNeKMSUzMLLnUxI0VSxvofKK6/y0Prq0a34k9Moe+MeJ0xT6I3vV1TjJ0t+EVTIFgtaFpEYPdh5zrxNVBUMBhPK2kd4aB0ZccM6WCW88fSY+XUi2VO+A1vfek20wOtki4WXfjxf1LRJsJCD/biSnwWjaIZDkvyoeuskSo87YQzJnd4w6XWiUcSMR0QFLYuZZtt6FPVCgMGGk+h8OSt6uDz9AcqX7kGZifUZlTtFdZIhAeMVUUGrYqZZa+ohrcvg4TG7fCnqayKPXrg37r2MA8oIx2rD1qMid16pTJmBjxbF1JfoNIK+RKcRdCE1guHchT7NhFZipoZWw9DQkKaEnKnooVUj6EJqBF1IjWDo7++f8Tly3rx5opa6GCT6TmoGMzAwoAupBUhILaALqSWPdHg6RTM8nRFOd7jzRC11iSTkpUuX+PHRo0f8GMg33zyCzVYsWsmBKmReXoAoTLjo0pK4nZoW8uRJP+z20Z/v/v37+GNdHSpef11YkgN1+kHCqIXLGNAOU8ZU2u+CxWCAIaQ4msX5RKHXt7hGb0uZBMgrOx+wj8zK2RvpY3/2aYALSc+dmz19StlmlviRPI7KXzY8VusXDuVCflA99Cc6hQfhYymY0jAvvoPo3jfBN3+SBe3r64PP54P07w4MXH+Ib7+aikcnfsIuCDx58kTUZJQ8MTw8zAuRnt7Nj3Fh/BmWtF+DTzSTHQ+LPCQiBaDe3n6MXG1ARkafODsOrlejaO5czHW2CgNxHdVFzFZUzWrjhwtJb46evI6ODpw/f56foKNSCA/Lo2fOnEFd3SneHhf+fnQXLoaJ1wNDrwUu8aD7XRY1DFvIGOpxQe1rqNxYhvb2MpiEbdT1CWBn44a1a9fyXLlp05u8pGcXY9s2M5rYrOWk38/Lgwcs5sZKQQEKen0vRLvegrqLop4AqkdmZWUhPz8fy5cv5206KuXOnQV44w0Tr+cWvxn7U0k3WNxUXkzXsLvNCSO75a6NtSj1KSG3FLUbSYhmVJYtQZMIwyirZJZoLMbO4wdRSCGcv26810fn5s2bfHBDhbzybxcfcjvdp18XzOFCU74sLT0Qh5glKMmpQ4ui5CB7IjZvRoFojhcu5PBwFm+Eg0Lp/Pm3ed33v9n8GDNBOdKHg4UeeOnO+htQ297ON3DJApNX1aLBb8Ji1mc1eVNDMdokN1bJrxQjiV4fDO0OvHr1Ki+PH5/HrLtf4Ns04eUeNkbwOHi1pmZbXGJa1+SgTijZ2tiLkjWLeJ2jhF9eilAtBL9eXSRsc1EkjIE2LuSzZ1/ykagSSvtnzcKpU6fUcEoe+emnPvQ0fMLL8HBwDo0NI5y77fBwJQm77DlqaYPTyPq0ye3d10xMZEecHpXo9cFkZmYGRSYKsyp2OyturFjxPWz/6CNuIjFjYiETrq6FhddWNPaWwLpQ2ClfOupQ0jWEoSFWukpQ56Dc2YpDu3LwGbftB3YdYpZgmxpaaR6pvGEKFytXrlTDKYXSvF/lITdXzhHPnj1jT+sScWUcrLLB7vGi2ViMUsU7CZ4v2U3nRzlfrnKTB3ejHyEDJN81tIvqKMJdn1iaVKcePLwGOhx5o8PBxc7L62S6MmFjnVZnWlmAHcBgayOO5ZiQKcxyvryIXWbhkeZduHiRwvBCLCo4hvXkjS1WtA65UIRgmypkIBRKldGpGk5vsMnzczlHjB859HmbyXOawGKgCK2UL1kYNDrBUp4IuSbUlh5nXroKOw9283DJ++7rRqF4NQ4fCVMuZg9C2OtFvwR46fl9fJ32Q7mhzCGZN8Lt5kL/fkWtLKJybkwyYS3pRUVFLzavKRI2hc2yl6mlFTvYw7KjVW6XD5iZyE7mj8E2vrJz+PBhZGdn8zdFYVQhcFBDeZRCsMLHH19io1z2YVKcsVZ2yBNVEdnDTCmIr/gwbyTlbu72Yl+TjXmlmzmpB+fOVch9w0H5j13mbt2BTKqbB1BO3qXarWgpMmOgfAgu0lfp07UIFWYKuSQqTVdY53IWdte/sKlCnjhxgodLmh+GHonAukJbW5uopS4xC8lE9CAflp4yPg0h6BwNcO7du4f+/llsTBGHkMLECbK3wjl3PY7xEwXYz4WiLkUw75LnKQX7u9DKjIE2/duPKEKazRK6ugxBuS+jp08W0pHP2x3bathgZzubijCPHEvISUQXMoqQnk4PctlYoScjQ1iB31l+zoWkOSWfioi8SGnoww+/0IWcLiIJGQmvt4ELFgldyGkiXiGTFV1IJqQWmPFCagXDZ/84pQupAabcI2/fvo0FC14sOiQDyfie4iXsEp1O6qELqRF0ITVC8gnZ7IDBsAxVCX79NNNIOiGbvR7Y7bmobdSVjIckE9KPgR47bG4bcmsbxQYrnVhILiH9jajNtWEVTMhGLVSn9FdhmfLFMpVlVbLIQfaZHY6TSkh/Yy1ybbRdyog1pRDh1Y+qTU7kNom9PT4XLHJvZg/ZibdJCDwToQWBqeTWrVuiFopPcln47xm+KBaX5PO5JAsdRS8mpNymY2BfXiySS+0YO5HfU+qQPB7Jw2qT7F28+OCi8Bp1W3roTrwLeGcC9uikIkkjJIVVZPM96AIRXgcWoRROVIbuazSuQaklcCce5cvEtj+mNOxJnlLCh7EmyR4uLPLwaZeaQsOoGmrpusTCKqGF0Jp6i+a0YOC1QXInsoc8GH3RfEpohiNw6rG6B66dEyeiVtC/xmLoHqmTNOhCaoRp+XVI+gcV+p+FZDnOmTNHvLPUZcpz5MOHD/HfZ8kTCH70/dl8MSHVmZY7SjcvWSCP1ALTIuTdB6N/hGi6oPCqBXSP1LZH0iR88tYtx/LIz9+dzcXm5d0zwhrZngga9Ug/qpbRCsrqWH4OadxE88gbNSuwAX/nYt998BXev/oadp6NbE8UrXhkhFEreaQXtgR/FSMc8Y5aScDfwo2G7eovJnAi2eOFHip91DpOonlkMIM44/0Si42hYkWyx48+ak2AWEetN2oc2IM/4zcrhEEQyT4e9FFrAsTikRQ6X/H+Ev9s2YGfChsRyT5edI9MgLE8cqpEJHSPTICoHnn2Hbzyhxz8NVSsSPYE0T0yAaJ55OdNR9nfo9jAxCbBqRTXDEa0J4pWPHJaFs3p5sU64Jlsvvud55r49iOpR61TgZ4jEyBqjpxi9ByZALpHTjy6R2plrZV9kGlZaCRPoJuYDEctrLVO+ahVZ3KYltCqM/HoQmoEXUhNAPwf9KNGXjVqwHsAAAAASUVORK5CYII=

  • 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

  • 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:-)

  • 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

  • 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:-)

  • 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. 

  • 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:-)

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply