question

billybox avatar image
billybox 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
calculations
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rows or columns? And what datatypes? An example of some data would be good.
0 Likes 0 ·
oh sorry .. below is enough ? profile (table) YearofBirtday (row) exapmle. 1961 monthofBirthday (row)example. 11 dayOfBirthday (row) example. 23
0 Likes 0 ·

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
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
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
Ha! Always forget about the 'new' functions
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.