In TSQL, how would you convert from int to datetime and give age?

Question

What will be sql for next,

I have a date of birth in an int field,

those. YYYYMMDD = 19600518

I would like to get age.

Answer 1

None of the other answers actually calculate age. They count the number of year borders and do not take birthday into account.

To calculate age, you need to do something like this:

DECLARE @d DATETIME SET @d = CONVERT(DATETIME, CONVERT(VARCHAR(8), 19600518), 112) SELECT DATEDIFF(year, @d, GETDATE()) - CASE WHEN DATEADD(year, DATEDIFF(year, @d, GETDATE()), @d) <= GETDATE() THEN 0 ELSE 1 END AS Age 

Answer 2

Most of the other answers are not designed for age - whole years (for example, January 1, 2009 - one year after December 31, 2008). Thus, if you use most of the calculations on this page, on average you return the wrong age for half a year. Luke is the only person who has seen this, but his answer seems too complicated to me - there is an easier way:

 Select CAST(DATEDIFF(hh, [birthdate], GETDATE()) / 8766 AS int) AS Age 

(NOTE: Thank you for the “Training” for making a wonderful catch using my original algorithm - this is an audit that uses hours instead of days).

Since rounding is very grainy here, it is almost perfectly accurate for every day of every year. The exceptions are so confusing that they are almost humorous: every fourth year returns at an age even younger if we A) ask for the age before 6:00 in the morning, B) for the person’s birthday, and C) their birthday after February 28th. Of course, depending on what time someone was born, this may be “technically” correct! In my setup, this is a perfectly acceptable compromise.

Here is a cycle that prints ages to show that it works.

 Declare @age int; Declare @BirthDate datetime; Declare @Year int; Set @Year = 2008; WHILE (@Year > 1930) BEGIN -- Put today date where you see '-03-18' SET @BirthDate = CAST(Cast(@Year as varchar(4)) + '-03-18' AS DATETIME) SELECT @age=CAST(DATEDIFF(hh, @BirthDate, GETDATE()) / 8766 AS int); Print Cast(@Year as varchar) + ' Age: ' + Cast(@age as varchar); Set @Year = @Year - 1; END; 

Finally, this is the version that also converts the date of the integer Paul to a real date:

 CAST(DATEDIFF(hh, Convert(Datetime, Convert(varchar(8), [birthdate]), 112), GETDATE()) / 8766 AS int) AS Age 

Answer 3

 DECLARE @dateSt VARCHAR(8) DECLARE @startDt DATETIME -- Set the start date string SET @dateSt = '19600518' -- Make it a DATETIME (the ISO way) SET @startDt = CAST(SUBSTRING(@dateSt, 1, 4) + '-' + SUBSTRING(@dateSt, 5, 2) + '-' + SUBSTRING(@dateSt, 7, 2) AS DATETIME) -- Age in Days SELECT DATEDIFF(D, @startDt, getdate()) 

Answer 4

Age in years:

 select datediff(YY, convert(datetime, convert(varchar, 19600518)), getdate()) 

Answer 5

 [EDIT] -- I forgot to declare the variables declare @birthday datetime; set @birthday = convert(datetime,convert(varchar, 19600518), 112); declare @datetoday datetime; set @datetoday = getdate(); select ( CASE WHEN dateadd(year, datediff (year, @birthday, @datetoday), @birthday) <= @datetoday THEN datediff (year, @birthday, @datetoday) ELSE datediff (year, @birthday, @datetoday) - 1 END) as age; 

Answer 6

Here's a one-line way to do this:

 CONVERT(DATETIME, CONVERT(VARCHAR(8),19600518), 112) 

But be careful! It depends on T-SQL and probably will not work in other SQL environments.

Note that 112 style is just the yyyymmdd ISO date format. (Something I found in the CONVERT documentation.)

Answer 7

I managed and got the same as @Learning

 select dob, datediff(year, convert(datetime, convert(varchar(8),[dob])) ,getdate()) as age from [mytable] where IsDate(convert(varchar(8),[dob])) = 1 

NB. I need IsDate, and there were also some invalid dates in the data.

Change Here is an article from SQLServerCentral for calculating age.

Answer 8

This is the reason you should never store dates with anything other than the datetime data type. The best solution is to change your data type and convert all dates once (don't be surprised if there are several invalid ones). then you no longer have to follow these workarounds.