InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 26 May 2013 00:09:45 GMT20http://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx[quote][b]onecaring (10/13/2011)[/b][hr]Interesting find. When does someone turn [b]EXACTLY[u][/u][/b] 1 year old? Or any full year old? Or when does 24 O'Clock happen? I guess it does happen but for such a nano second that it is almost impossible to get to that fraction. Or is it save to say never?One is either one this side of the line or on the other side but never ON the line when it comes to such precision like exactly one year old or exactly 24 O'Clock time.It is only my opinion... no one has to agree but positive criticism is most welcome :-D[/quote]With the precision of years/months/days, I'd say they're exactly one year old on their birthday.Thu, 13 Oct 2011 07:51:12 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspxInteresting find. When does someone turn [b]EXACTLY[u][/u][/b] 1 year old? Or any full year old? Or when does 24 O'Clock happen? I guess it does happen but for such a nano second that it is almost impossible to get to that fraction. Or is it save to say never?One is either one this side of the line or on the other side but never ON the line when it comes to such precision like exactly one year old or exactly 24 O'Clock time.It is only my opinion... no one has to agree but positive criticism is most welcome :-DThu, 13 Oct 2011 07:46:25 GMTonecaringhttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx[quote][b]onecaring (10/12/2011)[/b][hr]/*Just use this function and get the exact age in "YYYMMDD" format (YearsMonthsDays)average days in a year: 365.2425average days ion a month: 30.436875USAGE: SELECT dbo.udf_CalcAge('1960-01-01', '2011-10-12')RESULT: "0510911" -- 51 YEARS, 9 MONTHS AND 11 DAYS*/CREATE FUNCTION [dbo].[udf_CalcAge] (@StartDate datetime = NULL, @EndDate datetime = NULL)RETURNS varchar(7) WITH EXECUTE AS CALLERASBEGIN RETURN ( RIGHT('000' + CAST(FLOOR(DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) AS VARCHAR(3)), 3)+ RIGHT('00' + CAST(FLOOR((DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) % 1 * 12) AS VARCHAR(2)), 2) + RIGHT('00' + CAST(FLOOR(ROUND(((DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) % 1 * 12) % 1 * 30.436875, 0)) AS VARCHAR(2)), 2) ) END;[/quote]Interesting, if I set @StartDate = '1/1/2009' and @EndDate = '1/1/2010', I get 0001130, not one year. setting @EndDate = '1/2/2010' I get 0010001.Wed, 12 Oct 2011 10:44:35 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx/*Just use this function and get the exact age in "YYYMMDD" format (YearsMonthsDays)average days in a year: 365.2425average days ion a month: 30.436875USAGE: SELECT dbo.udf_CalcAge('1960-01-01', '2011-10-12')RESULT: "0510911" -- 51 YEARS, 9 MONTHS AND 11 DAYS*/CREATE FUNCTION [dbo].[udf_CalcAge] (@StartDate datetime = NULL, @EndDate datetime = NULL)RETURNS varchar(7) WITH EXECUTE AS CALLERASBEGIN RETURN ( RIGHT('000' + CAST(FLOOR(DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) AS VARCHAR(3)), 3)+ RIGHT('00' + CAST(FLOOR((DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) % 1 * 12) AS VARCHAR(2)), 2) + RIGHT('00' + CAST(FLOOR(ROUND(((DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) % 1 * 12) % 1 * 30.436875, 0)) AS VARCHAR(2)), 2) ) END;Wed, 12 Oct 2011 10:35:14 GMTonecaringhttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspxHere is another way I calculate age. SELECT @AGE = ' '+CAST(((datediff(ss, @DOB,GETDATE())) /31536000) AS NVARCHAR(MAX)) + ' Years, ' + CAST((((datediff(ss, @DOB,GETDATE())) %31536000) /2628000) AS NVARCHAR(MAX)) + ' Months, ' + CAST((((datediff(ss, @DOB,GETDATE())) %2628000) /86400) AS NVARCHAR(MAX)) + ' Days, ' + CAST((((datediff(ss, @DOB,GETDATE())) %86400) /3600) AS NVARCHAR(MAX)) + ' Hours, ' + CAST((((datediff(ss, @DOB,GETDATE())) %3600) /60) AS NVARCHAR(MAX)) + ' Minutes, ' + CAST(((datediff(ss, @DOB,GETDATE())) %60) AS NVARCHAR(MAX)) + ' Seconds 'Fri, 23 Oct 2009 11:01:11 GMTrbartram-847800http://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx[quote][b]David. (8/27/2009)[/b][hr]This demonstrates that the datediff solution does not change the age at the time of the birthdate. All the age results are the same: declare @BirthDate DATETIME set @BirthDate = '08/27/1980' print @BirthDate print getdate() SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE()) print 'TODAY IS THE TWENTY-SIXTH' set @BirthDate = '08/26/1980' print @BirthDate print getdate() SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE()) set @BirthDate = '08/25/1980' print @BirthDate print getdate() SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE()) set @BirthDate = '08/24/1980' print @BirthDate print getdate() SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE()) [/quote]Your right, DATEDIFF(yy, @birthdate, getdate()) will return the same value regardless if the @birthdate is before on or after GETDATE(). That is the nature of the DATEDIFF function. TO actually calculate age requires additional calculations to make the final determination.Consider this, DATEDIFF(yy,'2008-12-31','2009-01-01') returns 1 as there is a difference in year periods is 1 even though there is actually only a difference of 1 day.Thu, 27 Aug 2009 08:57:34 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspxThis demonstrates that the datediff solution does not change the age at the time of the birthdate. All the age results are the same: declare @BirthDate DATETIME set @BirthDate = '08/27/1980' print @BirthDate print getdate() SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE()) print 'TODAY IS THE TWENTY-SIXTH' set @BirthDate = '08/26/1980' print @BirthDate print getdate() SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE()) set @BirthDate = '08/25/1980' print @BirthDate print getdate() SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE()) set @BirthDate = '08/24/1980' print @BirthDate print getdate() SELECT 'DATEDIFF AGE = ', DATEDIFF(YEAR, @birthdate , GETDATE()) The following translates the dates into YYYYMMDD and gets the correct age, changing at at the month and year of the birtdate. DECLARE @CURRENT_YEAR INTEGER , @CURRENT_MONTHS INTEGER , @CURRENT_DAYS INTEGER , @CURRENT_YYYYMMDD INTEGER , @AGE_INTEGER INTEGER SET @CURRENT_YEAR = DATEPART(YEAR, GETDATE()) SET @CURRENT_MONTHS = DATEPART(MONTH, GETDATE()) SET @CURRENT_DAYS = DATEPART(DAY, GETDATE()) SET @CURRENT_YYYYMMDD = (@CURRENT_YEAR * 10000) + (@CURRENT_MONTHS * 100) + @CURRENT_DAYS SELECT 'CURRENT_DATE = ' , @CURRENT_YYYYMMDD declare @BirthDate DATETIME , @YEARS INTEGER , @MONTHS INTEGER , @DAYS INTEGER , @BIRTH_YYYYMMDD INTEGER set @BirthDate = '08/27/1980' set @YEARS = DATEPART(YEAR, @BIRTHDATE) SET @MONTHS = DATEPART(MM, @BIRTHDATE) SET @DAYS = DATEPART (DD, @BIRTHDATE) SET @BIRTH_YYYYMMDD = (@YEARS * 10000) + (@MONTHS * 100) + @DAYS SET @AGE_INTEGER = (@CURRENT_YYYYMMDD - @BIRTH_YYYYMMDD) / 10000 SELECT 'BIRTH DATE = ' , @BIRTH_YYYYMMDD SELECT '@AGE_INTEGER = ' , @AGE_INTEGER PRINT 'TODAY IS THE TWENTY-SIXTH' set @BirthDate = '08/26/1980' set @YEARS = DATEPART(YEAR, @BIRTHDATE) SET @MONTHS = DATEPART(MM, @BIRTHDATE) SET @DAYS = DATEPART (DD, @BIRTHDATE) SET @BIRTH_YYYYMMDD = (@YEARS * 10000) + (@MONTHS * 100) + @DAYS SET @AGE_INTEGER = (@CURRENT_YYYYMMDD - @BIRTH_YYYYMMDD) / 10000 SELECT 'BIRTH DATE = ' , @BIRTH_YYYYMMDD SELECT '@AGE_INTEGER = ' , @AGE_INTEGER set @BirthDate = '08/25/1980' set @YEARS = DATEPART(YEAR, @BIRTHDATE) SET @MONTHS = DATEPART(MM, @BIRTHDATE) SET @DAYS = DATEPART (DD, @BIRTHDATE) SET @BIRTH_YYYYMMDD = (@YEARS * 10000) + (@MONTHS * 100) + @DAYS SET @AGE_INTEGER = (@CURRENT_YYYYMMDD - @BIRTH_YYYYMMDD) / 10000 SELECT 'BIRTH DATE = ' , @BIRTH_YYYYMMDD SELECT '@AGE_INTEGER = ' , @AGE_INTEGER set @BirthDate = '08/24/1980' set @YEARS = DATEPART(YEAR, @BIRTHDATE) SET @MONTHS = DATEPART(MM, @BIRTHDATE) SET @DAYS = DATEPART (DD, @BIRTHDATE) SET @BIRTH_YYYYMMDD = (@YEARS * 10000) + (@MONTHS * 100) + @DAYS SET @AGE_INTEGER = (@CURRENT_YYYYMMDD - @BIRTH_YYYYMMDD) / 10000 SELECT 'BIRTH DATE = ' , @BIRTH_YYYYMMDD SELECT '@AGE_INTEGER = ' , @AGE_INTEGERThu, 27 Aug 2009 08:07:19 GMTRemove from listhttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspxFor calculating age in the form of x Years y Months z Days check the post of mine[url=http://aspxdev.blogspot.com/2008/09/get-date-diference-in-form-of-x-years-y.html]http://aspxdev.blogspot.com/2008/09/get-date-diference-in-form-of-x-years-y.html[/url]Thu, 02 Apr 2009 01:02:23 GMTaalokitoaamihttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx[quote][b]ScottPletcher (3/16/2009)[/b][hr][quote] And Lynn's method declares the leap-year person to be 18 on the 28th. [/quote]Yes, but I think someone made that point that legally in some jurisdictions, a person with a birthday of Feb 29 is legally older on Feb 28. This is inconvenient for IT people, but if it's a legal requirement, we must handle it. :)[/quote]I also put a disclaimer in my article regarding leaplings. For purposes of my article I made a "business" decision to keep their birthday in February. Based on legal requirements, this could change, and we would then have to account for that in our calculations.Mon, 16 Mar 2009 15:01:19 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx[quote] And Lynn's method declares the leap-year person to be 18 on the 28th. [/quote]Yes, but I think someone made that point that legally in some jurisdictions, a person with a birthday of Feb 29 is legally older on Feb 28. This is inconvenient for IT people, but if it's a legal requirement, we must handle it. :)Mon, 16 Mar 2009 14:23:14 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx'works out the age of the apprentice. Dim total As String Dim strDOB As String = Format(CDate(.DateOfBirth), "MM dd") Dim todaysdate As String = Format(CDate(Date.Today), "MM dd") If strDOB > todaysdate Then strDOB = Format(CDate(.DateOfBirth), "yyyy") ''''''''''''''''''''''Hasn't had birthday yet this year todaysdate = Format(CDate(Date.Today), "yyyy") total = todaysdate - strDOB - 1 Else strDOB = Format(CDate(.DateOfBirth), "yyyy") ''''''''''''''''''''''Has had birthday this year todaysdate = Format(CDate(Date.Today), "yyyy") total = todaysdate - strDOB End If p1_txtAge.Value = total p1_txtAge.Flatten = Truethis is something i wrote in VB.NETdon't know if its any more accurate when working out the leap year issueMon, 16 Mar 2009 10:49:53 GMTdavidandrews13http://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspxTim,Exactly!Suppose the case of my friend, who is born on May 11th, 1993. On their 18th birthday, the system using 365.25 will declare them still 17 - which can get them into all kinds of trouble depending on how they choose to celebrate legal drinking age (in Australia).On the other hand, someone born on Feb 29th, 1992 will still be considered legally 17 on Feb 28th, 2010, and shouldn't be able to go drinking that day (or worse, be tried as an adult for some crime).This is why using the YYYYMMDD method works best.20110511 - 19930511 = 18000020100228 - 19920229 = 179999datediff(day,'19930511','20110511') / 365.25 = 17.998631And Lynn's method declares the leap-year person to be 18 on the 28th.Anyway - I think we all agree that 'age' can be awkward if the method for calculating isn't great.RobSun, 15 Mar 2009 19:36:26 GMTRob Farleyhttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx[quote][b]rmattaway (3/13/2009)[/b][hr]One method used by my employer is what we call the window of time. It was decided that everyone in our databases could not be older than 120 years. As such an Age Table was created with the number of days, weeks, months, years, etc. This assures that no matter what you can compute the days between using datediff then look up the age you want to use. This method assures that apples are truly compared with apples because someone that is 3652 days old is the same age as someone else 3652 days old.[/quote]Most of the day-based solutions suffer from a fatal flaw:My birthdays are [b]never[/b] 365.25 (or .242199, or .2425) days apart.They are [b]either 365 or 366 days apart[/b], with the number of days depending on:1. Leap Years (this year or last year)2. When I was born in the year: Before Feb 29th, on Feb 29th or after Feb 29th3. The rules for Leapling birthdays in non-Leap YearsAnd for most real-world applications, being exact is important.I want to be able to drink, drive, receive benefits, and get paid more [i]on my birthday[/i].Approximations just aren't good enough :)Sun, 15 Mar 2009 18:11:15 GMTTim Wilson-Brownhttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspxYes you are correct it recalcs.Sun, 15 Mar 2009 09:15:28 GMTjustsamsonhttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx[quote][b]samsonjr2 (3/14/2009)[/b][hr]I like to use computative column with datediff to calculate the age.[/quote]Yes, its a shame you can't make it 'Persited' though, this means SQL has to recalc it with every query that includes that column.Sun, 15 Mar 2009 09:06:03 GMTC# Gnuhttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspxI like to use computative column with datediff to calculate the age.Sat, 14 Mar 2009 17:51:29 GMTjustsamsonhttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx[quote][b]Andrew (3/13/2009)[/b][hr][quote][b]Richard Briggs (3/13/2009)[/b][hr][quote][b]Andrew (3/13/2009)[/b][hr][quote][b]Richard Briggs (3/13/2009)[/b][hr]I guess it depends on the application, may be relevant to birthday money transfers, birthday card emails sites etc.[/quote]Perhaps, but everyone isn't born at midnight either, so you would need to store a full timestamp of the birthdate including timezone information. I doubt any of those types of applications have that level of detail.Andrew[/quote]I wouldn't store timestamp:Suppose AutoBirthdaySurprise.com, and received a request to send money and an 'ecard' to little Tommy in Aukland Wellington, if I was hosting in the UK and didn't account for the timezone difference little tommy wouldn't get his card or his money until after 1pm :w00t:[/quote]That's kind of my point. If you're going to be that picky, you have to not only store the timestamp, you also have to correctly account for the timezone difference. Otherwise, it will be off somewhere.Andrew[/quote]No you wouldn't need the timestamp, just shift the calculation using available timezone lookup data like this :"67330048","67330559","US","UNITED STATES","TEXAS","DALLAS","32.7961","-96.8024","75201","-06:00""67330560","67330815","US","UNITED STATES","MISSOURI","COLUMBIA","38.9424","-92.3145","65201","-06:00""67330816","67331071","US","UNITED STATES","TEXAS","DALLAS","32.7961","-96.8024","75201","-06:00""67331072","67331327","US","UNITED STATES","FLORIDA","BRADENTON","27.4649","-82.5465","34201","-05:00""67331328","67331583","US","UNITED STATES","ARKANSAS","TEXARKANA","33.4765","-93.9191","71854","-06:00""67331584","67331839","US","UNITED STATES","TEXAS","PLANO","33.0404","-96.7238","75023","-06:00"As I say, only useful for some applications, but interesting all the same.BrigzySat, 14 Mar 2009 05:38:23 GMTC# Gnuhttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspxGSquared, you're welcome. Hope that method works for you (YYYYMMDD).RobFri, 13 Mar 2009 23:39:27 GMTRob Farleyhttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx[quote][b]Richard Briggs (3/13/2009)[/b][hr][quote][b]Andrew (3/13/2009)[/b][hr][quote][b]Richard Briggs (3/13/2009)[/b][hr]I guess it depends on the application, may be relevant to birthday money transfers, birthday card emails sites etc.[/quote]Perhaps, but everyone isn't born at midnight either, so you would need to store a full timestamp of the birthdate including timezone information. I doubt any of those types of applications have that level of detail.Andrew[/quote]I wouldn't store timestamp:Suppose AutoBirthdaySurprise.com, and received a request to send money and an 'ecard' to little Tommy in Aukland Wellington, if I was hosting in the UK and didn't account for the timezone difference little tommy wouldn't get his card or his money until after 1pm :w00t:[/quote]That's kind of my point. If you're going to be that picky, you have to not only store the timestamp, you also have to correctly account for the timezone difference. Otherwise, it will be off somewhere.AndrewFri, 13 Mar 2009 22:40:50 GMTAndrew in WVhttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspxOne method used by my employer is what we call the window of time. It was decided that everyone in our databases could not be older than 120 years. As such an Age Table was created with the number of days, weeks, months, years, etc. This assures that no matter what you can compute the days between using datediff then look up the age you want to use. This method assures that apples are truly compared with apples because someone that is 3652 days old is the same age as someone else 3652 days old.Fri, 13 Mar 2009 16:07:31 GMTrmattawayhttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspxThis method includes a form of "every 4 years" leap year support. It assumes a lifetime will rarely exceed 99 years and so foregoes making allowances for the 100 year (and beyond) leap year aberrations. In addition, this method can be easily used directly in a select statement. Of course the floating point math will not yield maximum performance. Opinions on this are greatfully solicited.DECLARE @Years INTDECLARE @DOB DATETIMEDECLARE @Compare DATETIMESELECT @Years = ROUND((CONVERT(FLOAT,DATEDIFF(DAY,@DOB,@Compare)) - CONVERT(FLOAT,DATEDIFF(YEAR,@DOB,@Compare)) / CONVERT(FLOAT,4)) / CONVERT(FLOAT,365),0,3)Fri, 13 Mar 2009 14:10:39 GMTdeanroushhttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx[quote][b]Peso (6/30/2008)[/b][hr]E 12°55'05.25"N 56°04'39.16" [/quote]Lingongatan?Fri, 13 Mar 2009 12:14:30 GMTDPhillips-731960http://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx[quote][b]Richard Briggs (3/13/2009)[/b][hr]I wouldn't store timestamp:Suppose AutoBirthdaySurprise.com, and received a request to send money and an 'ecard' to little Tommy in Aukland Wellington, if I was hosting in the UK and didn't account for the timezone difference little tommy wouldn't get his card or his money until after 1pm :w00t:[/quote]How spoiled is little Tommy? You're sending him money, and he wants his 14 hours of interest?Fri, 13 Mar 2009 11:55:14 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx[quote][b]Andrew (3/13/2009)[/b][hr][quote][b]Richard Briggs (3/13/2009)[/b][hr]I guess it depends on the application, may be relevant to birthday money transfers, birthday card emails sites etc.[/quote]Perhaps, but everyone isn't born at midnight either, so you would need to store a full timestamp of the birthdate including timezone information. I doubt any of those types of applications have that level of detail.Andrew[/quote]I wouldn't store timestamp:Suppose AutoBirthdaySurprise.com, and received a request to send money and an 'ecard' to little Tommy in Aukland Wellington, if I was hosting in the UK and didn't account for the timezone difference little tommy wouldn't get his card or his money until after 1pm :w00t:Fri, 13 Mar 2009 11:53:13 GMTC# Gnuhttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspxHere is what I use:SELECT EMPLOYID, BRTHDATE, CASE WHEN DATEPART(day, BRTHDATE) > DATEPART(day, getdate()) THEN DATEDIFF(month, BRTHDATE, getdate()) - 1 ELSE DATEDIFF(month, BRTHDATE, getdate()) END / 12 AS [Age]FROM -MarkFri, 13 Mar 2009 11:50:08 GMTMark Wiley-448637http://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx[quote][b]Andrew (3/13/2009)[/b][hr][quote][b]Richard Briggs (3/13/2009)[/b][hr]I guess it depends on the application, may be relevant to birthday money transfers, birthday card emails sites etc.[/quote]Perhaps, but everyone isn't born at midnight either, so you would need to store a full timestamp of the birthdate including timezone information. I doubt any of those types of applications have that level of detail.Andrew[/quote]I wasn't born yesterday . . . . . . gotta get up early to pull one over on me . . .and other assorted cliche's:DFri, 13 Mar 2009 11:48:16 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx[quote][b]Richard Briggs (3/13/2009)[/b][hr]I guess it depends on the application, may be relevant to birthday money transfers, birthday card emails sites etc.[/quote]Perhaps, but everyone isn't born at midnight either, so you would need to store a full timestamp of the birthdate including timezone information. I doubt any of those types of applications have that level of detail.AndrewFri, 13 Mar 2009 11:42:16 GMTAndrew in WVhttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx[quote][b]jcrawf02 (3/13/2009)[/b][hr][quote][b]Richard Briggs (3/13/2009)[/b][hr][quote][b]jcrawf02 (3/13/2009)[/b][hr][quote][b]Richard Briggs (3/13/2009)[/b][hr]Comrades,Around one's birthday, we can be 16 in the UK, but still 15 in the US.Worth mentioning as may be relevant to certain age verification web applications.Brigzy16:36 UK GMT[/quote]sp_MSGoBackInTime strikes again. How can you possibly be 16 in the UK and 15 in the US, unless you're talking about the timezone difference?[/quote]Yes the TimeZone difference.[/quote]Would that really be relevant, even to web verification apps? (Not trying to be contentious, just curious). If the user is going to be the required age in a matter of hours, does it really matter if you allow them access?[/quote]I guess it depends on the application, may be relevant to birthday money transfers, birthday card emails sites etc.Fri, 13 Mar 2009 11:09:53 GMTC# Gnuhttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx[quote][b]Richard Briggs (3/13/2009)[/b][hr][quote][b]jcrawf02 (3/13/2009)[/b][hr][quote][b]Richard Briggs (3/13/2009)[/b][hr]Comrades,Around one's birthday, we can be 16 in the UK, but still 15 in the US.Worth mentioning as may be relevant to certain age verification web applications.Brigzy16:36 UK GMT[/quote]sp_MSGoBackInTime strikes again. How can you possibly be 16 in the UK and 15 in the US, unless you're talking about the timezone difference?[/quote]Yes the TimeZone difference.[/quote]Would that really be relevant, even to web verification apps? (Not trying to be contentious, just curious). If the user is going to be the required age in a matter of hours, does it really matter if you allow them access?Fri, 13 Mar 2009 10:54:29 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx[quote][b]jcrawf02 (3/13/2009)[/b][hr][quote][b]Richard Briggs (3/13/2009)[/b][hr]Comrades,Around one's birthday, we can be 16 in the UK, but still 15 in the US.Worth mentioning as may be relevant to certain age verification web applications.Brigzy16:36 UK GMT[/quote]sp_MSGoBackInTime strikes again. How can you possibly be 16 in the UK and 15 in the US, unless you're talking about the timezone difference?[/quote]Yes the TimeZone difference.Fri, 13 Mar 2009 10:49:01 GMTC# Gnuhttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx[quote][b]Richard Briggs (3/13/2009)[/b][hr]Comrades,Around one's birthday, we can be 16 in the UK, but still 15 in the US.Worth mentioning as may be relevant to certain age verification web applications.Brigzy16:36 UK GMT[/quote]sp_MSGoBackInTime strikes again. How can you possibly be 16 in the UK and 15 in the US, unless you're talking about the timezone difference?Fri, 13 Mar 2009 10:45:57 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspxComrades,Around one's birthday, we can be 16 in the UK, but still 15 in the US.Worth mentioning as may be relevant to certain age verification web applications.Brigzy16:36 UK GMTFri, 13 Mar 2009 10:38:32 GMTC# Gnuhttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspxafter doing my first ever post in life here I was bit disappointed from my own solution so here is the inspired from previous posts.DECLARE @BirthDate datetimeDECLARE @CurrentDate datetimeSELECT @BirthDate = '01 Jan 2008'SELECT @CurrentDate = '01 Jan 2009'SELECT CASE WHEN @BirthDate > @CurrentDate THEN 0ELSE ((DATEDIFF(mm,@BirthDate,@CurrentDate) - (CASE WHEN (DAY(@CurrentDate)-DAY(@BirthDate)) >= 0 THEN 0 ELSE 1 END)) /12) ENDFri, 13 Mar 2009 09:17:12 GMTPrashant-280564http://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx[quote][b]Lynn Pettis (3/13/2009)[/b][hr][quote][b]Richard Briggs (3/13/2009)[/b][hr][quote][b]Richard Briggs (3/13/2009)[/b][hr]Comrades,as regards my own post on creating a CALCULATED PERSISTED column containing 'Age'.I have discovered that actually because GetDate() is non-deterministic such a column cannot be made to PERSISTED. It is the fact that it is Persisted that would give me the performance benefit I would demand.Therefore I now have a new column Age im my Person table that is re-calulated by an SQL Server job, this runs shortly after midnight - using an UPDATE statement to recalcualte the Age for all records in my Person database.Now that I have this column (remember I am talking BIG database here many millions rows) I am able to index the column and my Query (WHERE Age > 50) is v.fast!;)Brigzy[/quote]And Comrades I use :[code]-- ==================================================-- Author: R.Briggs-- Create date: 13/3/09-- Description: Return age from DOB-- -- Age today:-- SELECT fn_GetAge (DateOfBirth, '25 December 2009 ') FROM Person---- Age on Christmas day: -- SELECT fn_GetAge (DateOfBirth, '25 December 2009 ') FROM Person---- ==================================================CREATE FUNCTION [dbo].[fn_GetAge] ( @pDateOfBirth DATETIME, @pAsOfDate DATETIME )RETURNS INTASBEGIN DECLARE @intAge INT IF @pDateOfBirth >= @pAsOfDate RETURN 0 SET @intAge = DATEDIFF(YY, @pDateOfBirth, @pAsOfDate) IF MONTH(@pDateOfBirth) > MONTH(@pAsOfDate) OR (MONTH(@pDateOfBirth) = MONTH(@pAsOfDate) AND DAY(@pDateOfBirth) > DAY(@pAsOfDate)) SET @intAge = @intAge - 1 RETURN @intAgeEND[/code]If you think its wrong - Please let me know!Brigzy[/quote]Please tell me that you ARE NOT updating every row in your database every night.[/quote]It takes sub 7 seconds when db out of use - but I agree it against my principles too.As I say you cannot persist a column that uses a function based on GetDate().I also have many developers who might have their own ways of calculating Age - as this thread clearly demonstrates! My solution ensures my data is not subject to any such variations to calulation method.BrigzyFri, 13 Mar 2009 08:49:18 GMTC# Gnuhttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspxI just did a pretty heavy-duty test of the YYYYMMDD method, and it seems to be plenty fast and was accurate for all dates I checked.Here's the test:[code]set nocount on;if object_id(N'tempdb..#T') is not null drop table #T;create table #T (BDay datetime primary key,Date datetime,Age as (cast(convert(varchar(25), Date, 112) as int)-cast(convert(varchar(25), bday, 112) as int))/10000);insert into #T (BDay)select dateadd(day, Number, '1/1/1800')from dbo.Numbers;insert into #T (BDay)select dateadd(day, Number, '1936-11-24 00:00:00.000')from dbo.Numbers;update #Tset Date = dateadd(day, abs(checksum(newid()))%(125*365), BDay);[/code]That gives semi-random ages for a full range of birthdays for the last two centuries and a significant part of this one.I checked a bunch of things with it, like leap years, Dec dates for Jan B-days, dates with the same month but the date is less than the birthday, and so on. Seemed to be accurate in everything I could throw at it.I don't have the time right now for a mathematical proof, but it does seem to work.I hadn't run into that method before, so thank you to the people who brought it up.Fri, 13 Mar 2009 08:42:56 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx[quote][b]Richard Briggs (3/13/2009)[/b][hr][quote][b]Richard Briggs (3/13/2009)[/b][hr]Comrades,as regards my own post on creating a CALCULATED PERSISTED column containing 'Age'.I have discovered that actually because GetDate() is non-deterministic such a column cannot be made to PERSISTED. It is the fact that it is Persisted that would give me the performance benefit I would demand.Therefore I now have a new column Age im my Person table that is re-calulated by an SQL Server job, this runs shortly after midnight - using an UPDATE statement to recalcualte the Age for all records in my Person database.Now that I have this column (remember I am talking BIG database here many millions rows) I am able to index the column and my Query (WHERE Age > 50) is v.fast!;)Brigzy[/quote]And Comrades I use :[code]-- ==================================================-- Author: R.Briggs-- Create date: 13/3/09-- Description: Return age from DOB-- -- Age today:-- SELECT fn_GetAge (DateOfBirth, '25 December 2009 ') FROM Person---- Age on Christmas day: -- SELECT fn_GetAge (DateOfBirth, '25 December 2009 ') FROM Person---- ==================================================CREATE FUNCTION [dbo].[fn_GetAge] ( @pDateOfBirth DATETIME, @pAsOfDate DATETIME )RETURNS INTASBEGIN DECLARE @intAge INT IF @pDateOfBirth >= @pAsOfDate RETURN 0 SET @intAge = DATEDIFF(YY, @pDateOfBirth, @pAsOfDate) IF MONTH(@pDateOfBirth) > MONTH(@pAsOfDate) OR (MONTH(@pDateOfBirth) = MONTH(@pAsOfDate) AND DAY(@pDateOfBirth) > DAY(@pAsOfDate)) SET @intAge = @intAge - 1 RETURN @intAgeEND[/code]If you think its wrong - Please let me know!Brigzy[/quote]Please tell me that you ARE NOT updating every row in your database every night.Fri, 13 Mar 2009 08:27:49 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx[quote][b]Andrew (3/13/2009)[/b][hr][quote][b]Richard Briggs (3/13/2009)[/b][hr]Comrades,as regards my own post on creating a CALCULATED PERSISTED column containing 'Age'.I have discovered that actually because GetDate() is non-deterministic such a column cannot be made to PERSISTED. It is the fact that it is Persisted that would give me the performance benefit I would demand.Therefore I now have a new column Age im my Person table that is re-calulated by an SQL Server job, this runs shortly after midnight - using an UPDATE statement to recalcualte the Age for all records in my Person database.Now that I have this column (remember I am talking BIG database here many millions rows) I am able to index the column and my Query (WHERE Age > 50) is v.fast!;)Brigzy[/quote]Would you not get the same benefit by approaching your query from the other side without the cost of updating "many millions" of rows every night? For any given date, it should be easy enough to calculate the minimum and maximum dates that will yield the age you are seeking, and then query patients whose DOB is between those dates. DOB could be indexed to give you the boost you're after.This is untested, but I think something like this should work:[code]DECLARE @age tinyintDECLARE @range_begin_date datetimeDECLARE @range_end_date datetimeSET @age = 50SELECT @range_begin_date = DATEADD(yy, -@age - 1, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)), @range_end_date = DATEADD(yy, -@age, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))SELECT @range_begin_date, @range_end_dateSELECT *FROM some_tableWHERE dob > @range_begin_date AND dob <= @range_end_date[/code]Andrew[/quote]Andrew that is nice.I do also have a fair number of screens/reports where lists of records need to display age, so for my purposes its easier for me to have Age pre-calced. I have to produce some stats too where I will age-banding, again (for my app at least) it will be easier/faster pre-calculated.Like your code solution ++good (Orwell)Fri, 13 Mar 2009 08:19:26 GMTC# Gnuhttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspxGood article, Lynn. Glad you mentioned the legal issues surrounding Leaplings.Fri, 13 Mar 2009 08:18:54 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspxThis is just what I have been looking for! I could have done it myself, but why reinvent the wheel, when you can buy one off the shelfFri, 13 Mar 2009 08:16:10 GMTascott-787611http://www.sqlservercentral.com/Forums/Topic525559-1323-1.aspx[quote][b]Richard Briggs (3/13/2009)[/b][hr]Comrades,as regards my own post on creating a CALCULATED PERSISTED column containing 'Age'.I have discovered that actually because GetDate() is non-deterministic such a column cannot be made to PERSISTED. It is the fact that it is Persisted that would give me the performance benefit I would demand.Therefore I now have a new column Age im my Person table that is re-calulated by an SQL Server job, this runs shortly after midnight - using an UPDATE statement to recalcualte the Age for all records in my Person database.Now that I have this column (remember I am talking BIG database here many millions rows) I am able to index the column and my Query (WHERE Age > 50) is v.fast!;)Brigzy[/quote]Would you not get the same benefit by approaching your query from the other side without the cost of updating "many millions" of rows every night? For any given date, it should be easy enough to calculate the minimum and maximum dates that will yield the age you are seeking, and then query patients whose DOB is between those dates. DOB could be indexed to give you the boost you're after.This is untested, but I think something like this should work:[code]DECLARE @age tinyintDECLARE @range_begin_date datetimeDECLARE @range_end_date datetimeSET @age = 50SELECT @range_begin_date = DATEADD(yy, -@age - 1, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)), @range_end_date = DATEADD(yy, -@age, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))SELECT @range_begin_date, @range_end_dateSELECT *FROM some_tableWHERE dob > @range_begin_date AND dob <= @range_end_date[/code]AndrewFri, 13 Mar 2009 08:02:54 GMTAndrew in WV