## Calculating age

 Author Message gilbert delarosa SSC-Enthusiastic Group: General Forum Members Points: 192 Visits: 2438 This seems to work`DECLARE @DOB DATETIMESET @DOB='9/19/2000'SELECT FLOOR(DATEDIFF(DD,@DOB,GETDATE())/365.25)` Jeff Moden SSC Guru Group: General Forum Members Points: 506343 Visits: 44265 gilbert delarosa (9/20/2013)This seems to work`DECLARE @DOB DATETIMESET @DOB='9/19/2000'SELECT FLOOR(DATEDIFF(DD,@DOB,GETDATE())/365.25)`Good try but it doesn't work...`DECLARE @DOB DATETIMESET @DOB='01/01/2013'SELECT FLOOR(DATEDIFF(DD,@DOB,'01/01/2014')/365.25)``Results:---------------------------------------0(1 row(s) affected)` --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs gilbert delarosa SSC-Enthusiastic Group: General Forum Members Points: 192 Visits: 2438 But they wouldn't be 1 until after their BDayor you could just `DECLARE @DOB DATETIMESET @DOB='01/01/2013'SELECT FLOOR((DATEDIFF(DD,@DOB,'01/01/2014')+1)/365.25)` Jeff Moden SSC Guru Group: General Forum Members Points: 506343 Visits: 44265 gilbert delarosa (9/20/2013)But they wouldn't be 1 until after their BDayor you could just `DECLARE @DOB DATETIMESET @DOB='01/01/2013'SELECT FLOOR((DATEDIFF(DD,@DOB,'01/01/2014')+1)/365.25)`Still doesn't work. Last I heard, you gained a year on your birthday and not the day before (Feb 29 babies sometimes excluded). :-)`DECLARE @DOB DATETIMESET @DOB='01/01/2012'SELECT FLOOR((DATEDIFF(DD,@DOB,'12/31/2012')+1)/365.25)``Results:---------------------------------------1(1 row(s) affected)` --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs mister.magoo One Orange Chip Group: General Forum Members Points: 26424 Visits: 7939 `declare @dob datetime,@now dateset @dob='1 jan 2012'set @now = '31 dec 2012'select datepart(year,@now) - datepart(year,@dob) + case when dateadd(day ,datepart(day,@dob)-1 ,dateadd(month ,datepart(month,@dob)-1 ,dateadd(year ,datepart(year,@now)-1800 ,'1 jan 1800' ) ) ) > @now then -1 else 0 end` MM`select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);`Forum Etiquette: How to post Reporting Services problemsForum Etiquette: How to post data/code on a forum to get the best help - by Jeff ModenHow to Post Performance Problems - by Gail Shaw ELSSON Grasshopper Group: General Forum Members Points: 16 Visits: 7 Is most simple: DECLARE @Birthday date = '1994/12/10' SELECT (CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT) - CAST(CONVERT(VARCHAR(8), @Birthday, 112) AS INT)) / 10000 AS Age Jeff Moden SSC Guru Group: General Forum Members Points: 506343 Visits: 44265 +xELSSON - Thursday, May 4, 2017 2:46 PMIs most simple: DECLARE @Birthday date = '1994/12/10' SELECT (CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT) - CAST(CONVERT(VARCHAR(8), @Birthday, 112) AS INT)) / 10000 AS AgeI've not checked some of the possibilities nor have I checked it for performance but THAT IS mighty interesting especially for how it correctly determines ages when a Leap Day is involved. --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs ELSSON Grasshopper Group: General Forum Members Points: 16 Visits: 7 +xJeff Moden - Friday, May 5, 2017 5:53 AM+xELSSON - Thursday, May 4, 2017 2:46 PMIs most simple: DECLARE @Birthday date = '1994/12/10' SELECT (CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS INT) - CAST(CONVERT(VARCHAR(8), @Birthday, 112) AS INT)) / 10000 AS AgeI've not checked some of the possibilities nor have I checked it for performance but THAT IS mighty interesting especially for how it correctly determines ages when a Leap Day is involved.Thanks, this solution is ideal for BI, since the IDs of the dates are numbers of the same (Example "2017/05/05" has ID 20170505), so that the IDs would be used directly. This method is based on mathematics and bit-shift (in this case decimal shift)