CODEWITHSUNDEEP
sqlsql-servert-sql
user9632326
user9632326

Reputation:

Calculate age on integer field

The date field I have to calculate age on is yyyymmdd.

I've tried below and converted getdate to yyyymmdd format but can't seem to get the age from that. I've tried with datediff but can't seem to get it to work either. 

 SELECT CLAIM.BTHDAT
     , (CONVERT(VARCHAR(8),GETDATE(), 112) - CLAIM.BTHDAT)/365.25   
 FROM CLAIM

Upvotes: 0

Views: 950

Answers (3)

Luis Cazares
Luis Cazares

Reputation: 3595

Here's an option to get the age accurately, with the assumption that all dates will follow the yyyymmdd format and will be valid dates.

SELECT CLAIM.BTHDAT
 ,DATEDIFF( YY, CONVERT(CHAR(8),CLAIM.BTHDAT), GETDATE()) 
   - CASE WHEN SUBSTRING( CONVERT( char(8), GETDATE(), 112), 5, 4) < SUBSTRING( CONVERT( char(8), BTHDAT), 5, 4)  THEN 1 ELSE 0 END
 FROM CLAIM

Upvotes: 0

Liaqat Kundi
Liaqat Kundi

Reputation: 71

try this....

SELECT CLAIM.BTHDAT, DATEDIFF(hour,CLAIM.BTHDAT,GETDATE())/8766.0 AS AgeYearsDecimal
    ,CONVERT(int,ROUND(DATEDIFF(hour,CLAIM.BTHDAT,GETDATE())/8766.0,0)) AS AgeYearsIntRound
    ,DATEDIFF(hour,CLAIM.BTHDAT,GETDATE())/8766 AS AgeYearsIntTrunc  FROM CLAIM

If you want to Age in Day,Month & year then you can try this.Create Function to Calcuate Age and call it in Query like this

    Create function [dbo].[GetAge](@dayOfBirth datetime, @today datetime)
       RETURNS varchar(100)
    AS

    Begin
      DECLARE @tmpdate datetime, @date datetime, @years int, @months int, @days int

    SELECT @tmpdate = @dayOfBirth

    SELECT @years = DATEDIFF(yy, @tmpdate, @today) - CASE WHEN (MONTH(@tmpdate) > MONTH(@today)) OR (MONTH(@tmpdate) = MONTH(@today) AND DAY(@tmpdate) > DAY(@today)) THEN 1 ELSE 0 END
    SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
    SELECT @months = DATEDIFF(m, @tmpdate, @today) - CASE WHEN DAY(@tmpdate) > DAY(@today) THEN 1 ELSE 0 END
    SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
    SELECT @days = DATEDIFF(d, @tmpdate, @today)

    return cast(@years as varchar(2)) + ' years,' + cast(@months as varchar(2)) + ' months,' + cast(@days as varchar(3)) + ' days'
    end


Select  CLAIM.BTHDAT,dbo.[GetAge](CLAIM.BTHDAT,getdate()) From CLAIM

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 82020

Assuming you want AGE in YEARS... How about datediff()

Select Age = datediff(YEAR,left(CLAIM.BTHDAT,8),getdate())
 From  CLAIM

Upvotes: 1

Related Questions