Calculating Age

• IceDread

  SSCertifiable

  Points: 5050

  More actions

  February 23, 2016 at 2:49 am

  #1860321

  C# Gnu (3/13/2009)

  ---

  Richard Briggs (3/13/2009)

  ---

  Comrades,

  as regards my own post on creating a CALCULATED PERSISTED column containing 'Age'.

  I have discovered that actually because GetDate() is non-deterministic such a column cannot be made to PERSISTED.

  It is the fact that it is Persisted that would give me the performance benefit I would demand.

  Therefore I now have a new column Age im my Person table that is re-calulated by an SQL Server job, this runs shortly after midnight - using an UPDATE statement to recalcualte the Age for all records in my Person database.

  Now that I have this column (remember I am talking BIG database here many millions rows) I am able to index the column and my Query (WHERE Age > 50) is v.fast!;)

  Brigzy

  And Comrades I use :

  -- ==================================================

  -- Author:R.Briggs

  -- Create date: 13/3/09

  -- Description:Return age from DOB

  --

  -- Age today:

  -- SELECT fn_GetAge (DateOfBirth, '25 December 2009 ') FROM Person

  --

  -- Age on Christmas day:

  -- SELECT fn_GetAge (DateOfBirth, '25 December 2009 ') FROM Person

  --

  -- ==================================================

  CREATE FUNCTION [dbo].[fn_GetAge] ( @pDateOfBirth DATETIME,

  @pAsOfDate DATETIME )

  RETURNS INT

  AS

  BEGIN

  DECLARE @intAge INT

  IF @pDateOfBirth >= @pAsOfDate

  RETURN 0

  SET @intAge = DATEDIFF(YY, @pDateOfBirth, @pAsOfDate)

  IF MONTH(@pDateOfBirth) > MONTH(@pAsOfDate) OR

  (MONTH(@pDateOfBirth) = MONTH(@pAsOfDate) AND

  DAY(@pDateOfBirth) > DAY(@pAsOfDate))

  SET @intAge = @intAge - 1

  RETURN @intAge

  END

  If you think its wrong - Please let me know!

  Brigzy

  Old thread awoken here but it was a good hit I got when searching on the issue.

  Would this not be faster? It's your code but modified so the performance hit of a scalar function should not interfere.

  CREATE FUNCTION [dbo].[GetAge]

  (

  @DateOfBirth DATETIME,

  @AsOfDate DATETIME

  )

  RETURNS TABLE

  AS

  RETURN

  (

  select case when @DateOfBirth >= @AsOfDate then 0

  when MONTH(@DateOfBirth) > MONTH(@AsOfDate)

  OR (MONTH(@DateOfBirth) = MONTH(@AsOfDate)

  AND DAY(@DateOfBirth) > DAY(@AsOfDate))

  then DATEDIFF(YY, @DateOfBirth, @AsOfDate) -1

  else DATEDIFF(YY, @DateOfBirth, @AsOfDate)

  end

  as Age

  )

  GO

• Manie Verster

  SSCertifiable

  Points: 7152

  More actions

  February 24, 2016 at 12:19 am

  #1860631

  Thank you Richard, you have just solved a problem for me of an old problem of giving a valid age. Yes, I know there are most probably many solutions out there but the problem has not been serious enough to actually do something about it and Lynn also had a very good solution in her article but you script just put the cherry on the cake.

  Thanks a mil.:-D:-D:-D:-D:-D

  :-PManie Verster
  Developer
  Johannesburg
  South Africa

  I can do all things through Christ who strengthens me. - Holy Bible
  I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  February 24, 2016 at 8:54 am

  #1860773

  Manie Verster (2/24/2016)

  ---

  Thank you Richard, you have just solved a problem for me of an old problem of giving a valid age. Yes, I know there are most probably many solutions out there but the problem has not been serious enough to actually do something about it and Lynn also had a very good solution in her article but you script just put the cherry on the cake.

  Thanks a mil.:-D:-D:-D:-D:-D

  I just have to correct you, I am a guy.

• Manie Verster

  SSCertifiable

  Points: 7152

  More actions

  March 15, 2016 at 12:54 am

  #1865124

  Sorry Lynn, my bad!:blush:

  :-PManie Verster
  Developer
  Johannesburg
  South Africa

  I can do all things through Christ who strengthens me. - Holy Bible
  I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

• aabidkhan84@gmail.com

  Newbie

  Points: 2

  More actions

  October 1, 2024 at 4:32 pm

  #4464258

  DECLARE @DOB DATETIME, @CurrDT DATETIME, @YY INT, @mm INT, @DD INT

  SET @CurrDT = GETDATE()

  SET @DOB = '1984-11-12'

  SET @YY = YEAR(@CurrDT) - YEAR(@DOB)

  SET @mm = MONTH(@CurrDT) - MONTH(@DOB)

  SET @DD = DAY(@CurrDT) - DAY(@DOB)

  IF(@DD < 0)

  BEGIN

  SET @DD = @DD + 30;

  SET @mm = @mm - 1

  END

  IF(@MM < 0)

  BEGIN

  SET @mm = @mm + 12;

  SET @YY = @YY - 1

  END

  SELECT CONVERT(VARCHAR(50),@YY) + ' Y, ' + CONVERT(VARCHAR(50),@MM) + ' M, ' + CONVERT(VARCHAR(50),@DD) + ' D'

• aabidkhan84@gmail.com

  Newbie

  Points: 2

  More actions

  October 1, 2024 at 4:43 pm

  #4464261

  DECLARE @DOB DATETIME, @CurrDT DATETIME, @YY INT, @mm INT, @DD INT

  SET @CurrDT = GETDATE()

  SET @DOB = CONVERT(DATETIME,@Filter1,101)--'1984-11-12'

  SET @YY = YEAR(@CurrDT) - YEAR(@DOB)

  SET @mm = MONTH(@CurrDT) - MONTH(@DOB)

  SET @DD = DAY(@CurrDT) - DAY(@DOB)

  IF(@DD < 0)

  BEGIN

  SET @DD = @DD + 30;

  SET @mm = @mm - 1

  END

  IF(@MM < 0)

  BEGIN

  SET @mm = @mm + 12;

  SET @YY = @YY - 1

  END

  SELECT= CONVERT(VARCHAR(50),@YY) + ' Y, ' + CONVERT(VARCHAR(50),@MM) + ' M, ' + CONVERT(VARCHAR(50),@DD) + ' D'