Home / T-SQL / Calculate Age in SQL Server

Calculate Age in SQL Server


Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /home/kraije21/domains/sqlblog.nl/public_html/wp-content/plugins/seo-auto-links/seo-auto-links.php on line 76

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /home/kraije21/domains/sqlblog.nl/public_html/wp-content/plugins/seo-auto-links/seo-auto-links.php on line 246

Check Also

T-TSQL – Create 4 week Moving Total


Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /home/kraije21/domains/sqlblog.nl/public_html/wp-content/plugins/seo-auto-links/seo-auto-links.php on line 76

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /home/kraije21/domains/sqlblog.nl/public_html/wp-content/plugins/seo-auto-links/seo-auto-links.php on line 246

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /home/kraije21/domains/sqlblog.nl/public_html/wp-content/plugins/seo-auto-links/seo-auto-links.php on line 76

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /home/kraije21/domains/sqlblog.nl/public_html/wp-content/plugins/seo-auto-links/seo-auto-links.php on line 246

3 comments

  1. Howard Rothenburg

    CREATE FUNCTION [dbo].[ufn_GetAge]
    (@pDateOfBirth DATETIME,
    @pAsOfDate DATETIME
    )
    RETURNS INT
    AS
    BEGIN
    DECLARE @vAge INT;
    IF @pDateOfBirth >= @pAsOfDate
    RETURN 0;
    SET @vAge = DATEDIFF(YY, @pDateOfBirth, @pAsOfDate);
    IF MONTH(@pDateOfBirth) > MONTH(@pAsOfDate)
    OR (MONTH(@pDateOfBirth) = MONTH(@pAsOfDate)
    AND DAY(@pDateOfBirth) > DAY(@pAsOfDate))
    SET @vAge = @vAge – 1;
    RETURN @vAge;
    END;
    GO

  2. Howard Rothenburg

    Select DATEDIFF(yy, patient.BirthDate, GETDATE()) – CASE
    WHEN MONTH(patient.BirthDate) > MONTH(GETDATE())
    OR (MONTH(patient.BirthDate) = MONTH(GETDATE())
    AND DAY(patient.BirthDate) > DAY(GETDATE()))
    THEN 1
    ELSE 0
    END AS Age

  3. There is no solution based that will always return correct results based on one DATEDIFF.

    For example, the formula DATEDIFF(HOUR,@DateofBirth,@ReportStartDate)/8766 return an incorrect result for date of birth 19 January 1994 and probe date 19 January 2016.

    The formula FLOOR(DATEDIFF(HOUR,@DateofBirth,@ReportStartDate )/8765.82) also fails for on a combination like that.

    The other formula’s are fine, except that the FLOOR() function in the last formula doesn’t add any value (since it acts on integer subtraction).

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *