asked
How can i calculate age from three data (YearofBirtday, monthofBirthday and dayOfBirthday)
i have a PROFILE table which has 3 rowas : YearofBirtday, monthofBirthday and dayOfBirthday how can i calculate the actual age ? thanks a lot guys
rows or columns? And what datatypes? An example of some data would be good.
Still not sure if you mean the data is stored as columns in one row, or you have a name-value pair, so here are examples of both. In both cases the calculation for the age is the same, most of the heavy-lifting is getting the Date of Birth (DoB) in the first place. declare @Profile table (YearofBirthday int, monthofBirthday int, dayOfBirthday int) insert into @Profile select 1970, 7, 16 insert into @Profile select 1970, 1, 16 select *, DATEDIFF(yy, DoB, getdate()) - (CASE WHEN DoB > DATEADD(yy, DATEDIFF(yy, DoB, getdate()) * -1, getdate()) THEN 1 ELSE 0 end) as Age from ( select *, dateadd(day, dayofBirthday-1, dateadd(month, monthofBirthday-1 ,dateadd(year, YearofBirthday-1900,0))) as DoB from @Profile ) a Name-value pair : declare @Profile table (name varchar(100), value int) insert into @Profile select 'YearofBirthday',1970 insert into @Profile select 'monthofBirthday',7 insert into @Profile select 'dayofBirthday',16 select *, DATEDIFF(yy, DoB, getdate()) - (CASE WHEN DoB > DATEADD(yy, DATEDIFF(yy, DoB, getdate()) * -1, getdate()) THEN 1 ELSE 0 end) as Age from ( select *, dateadd(day, dayofBirthday-1, dateadd(month, monthofBirthday-1 ,dateadd(year, YearofBirthday-1900,0))) as DoB from ( select (select value from @Profile where name = 'dayofBirthday') as dayofBirthday, (select value from @Profile where name = 'monthofBirthday') as monthofBirthday, (select value from @Profile where name = 'YearofBirthday') as YearofBirthday ) b ) a
If you are using SQL 2012 or above, you can use the [DATEFROMPARTS function](
https://msdn.microsoft.com/en-us/library/hh213228.aspx) to convert the three integers into a date, then use @Kev-riley's datediff calculations.
17 People are following this question.
