InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 22:58:45 GMT20http://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxThird option calucates wrong ageWed, 09 Nov 2011 02:39:39 GMTharsh.lapashiyahttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxI was robbed....:( the 3rd answer is wrong!Wed, 01 Jun 2011 12:11:53 GMTterrykzncshttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspx[quote][b]Jesse McLain (3/6/2008)[/b][hr]Sorry everyone, I really got egg on my face with this one. :ermm: When I posted the question, I copied the wrong answer for answer #3. The missing #4 is the only correct answer:[quote]DECLARE @BirthDate datetimeSET @BirthDate = '3/7/1908'SELECT --#1: DATEDIFF(yy, @BirthDate, GETDATE()), --#2: FLOOR(CONVERT(decimal(9, 2), DATEDIFF(d, @BirthDate, GETDATE())) / 365.0), -- #3: DATEDIFF(yy, @BirthDate, GETDATE()) - CASE WHEN DATEPART(m, @BirthDate) >= DATEPART(m, GETDATE()) AND DATEPART(d, @BirthDate) >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END, -- the missing #4: DATEDIFF(yy, @BirthDate, GETDATE()) - CASE WHEN DATEPART(m, @BirthDate) <; DATEPART(m, GETDATE()) OR (DATEPART(m, @BirthDate) = DATEPART(m, GETDATE()) AND DATEPART(d, @BirthDate) < DATEPART(d, GETDATE())) THEN 0 ELSE 1 END[/quote]Assuming that GETDATE() = '3/6/2008', answers 1-3 will return 100 as the age, whereas #4 will return 99, the correct age.Again, really sorry for the mixup. Cheers,Jesse[/quote]The missing #4 is not the correct answer, because each year it is 1 year out on the birthday (it's right all other days of the year).Thu, 22 Apr 2010 19:09:36 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxSpecified Reasons are quite enough to understand that why below method is more accurate.But actually author made a mistake here.I mean that Method is right but query written is logically wrong.Wrong one specified in problem:select DATEDIFF(yy, DateOfBirth, GETDATE()) - CASE WHEN DATEPART(m, DateOfBirth) >= DATEPART(m, GETDATE()) AND DATEPART(d, DateOfBirth) >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END Corrected one:select DATEDIFF(yy, DateOfBirth, GETDATE()) - CASE WHEN DATEPART(m, GETDATE() ) >= DATEPART(m, DateOfBirth) AND DATEPART(d, GETDATE()) >= DATEPART(d, DateOfBirth) THEN 0 ELSE 1 END :P:)Sun, 08 Mar 2009 07:43:58 GMTgyanp.garghttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxNone of the three options given are completely correct. For the same month, if the DOB has not yet arrived, all the three options give the wrong age.Tue, 04 Nov 2008 02:48:08 GMTSanjay Rohrahttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspx[quote][b]pjaime (7/17/2008)[/b][hr]Siendo hoy 17-07-2008declare @DateOfBirth datetimeset @DateOfBirth = '19660117'select DATEDIFF(yy, @DateOfBirth, GETDATE()) - CASE WHEN DATEPART(m, @DateOfBirth) >= DATEPART(m, GETDATE()) AND DATEPART(d, @DateOfBirth) >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END añosel resultado es: años ----------- 41entonces me equivoque al festejar mis 42 años !!!!!!!:w00t:[/quote]PJaime,Logical "AND" is not the same as concatenating first and then comparing. Look at your comparison with values substituted for the functions:DOBMonth=01DOBDay=17TodayMonth=07TodayMonth= 16Your original code evalutes to:[code]If 01 >= 07 and 17 >= 16 then 0 Else 1 -- FALSE. 1 is NOT greater than 7, so you subtract 1 from años[/code]You might use an OR to build code that results in this evaluatioin with the values given above:[code]If 01 < 07 OR (01 = 07 AND 17 <= 16)[/code]or you can concatenate the month and day parts before doing a single comparison[code]If 0117 <= 0716 [/code]Thu, 17 Jul 2008 09:44:28 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxSiendo hoy 17-07-2008declare @DateOfBirth datetimeset @DateOfBirth = '19660117'select DATEDIFF(yy, @DateOfBirth, GETDATE()) - CASE WHEN DATEPART(m, @DateOfBirth) >= DATEPART(m, GETDATE()) AND DATEPART(d, @DateOfBirth) >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END añosel resultado es: años ----------- 41entonces me equivoque al festejar mis 42 años !!!!!!!:w00t:Thu, 17 Jul 2008 07:14:31 GMTpjaimehttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxGianluca, You wrote:[quote] It looks like some queries work for some people and other queries work for some other people: the only thing I know is I would never put any of these in my code... [/quote]What would you not use? Why not? For optional extra credit, what would you use?Wed, 16 Jul 2008 09:23:25 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxIt looks like some queries work for some people and other queries work for some other people: the only thing I know is I would never put any of these in my code...:DWed, 16 Jul 2008 06:39:58 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxThere are many ways to give the right answer...Your solution is returning me a star character (*)... A dimension musl be provided to the @result variable declaration: Declare @result VarChar[b](10)[/b]... But don't use a local variable unless it is required...I really prefer an Integer output... Here is another solution...Declare @DateOfBirth DateTimeSet @DateOfBirth = '1983-07-10'Select (DateDiff(Year, @DateOfBirth, GetDate()) - Case When Convert(SmallDateTime, Convert(Char(4), DatePart(Year, GetDate()))+ SubString(Convert(Char(10), @DateOfBirth, 121), 5, 6))<= GetDate() Then 0 Else 1 End)Thu, 10 Jul 2008 08:49:39 GMTLuc Philiehttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxdeclare @DateOfBirth datetimedeclare @result as varcharset @DateOfBirth='1983-07-11'select @result = (DATEDIFF(yy, @DateOfBirth, GETDATE()) - case when Convert(Datetime,Convert(varchar,DATEPART(yy, GETDATE())) + '-' + Convert(varchar,DATEPART(m, @DateOfBirth))+ '-'+ Convert(varchar,DATEPART(d, @DateOfBirth)))<=GetDate() THEN 0 ELSE 1 END )select @ResultHere is the correct query to retrieve exact age.:cool:Thu, 10 Jul 2008 03:22:26 GMTMayureshhttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspx[quote][b]Luc Philie (7/3/2008)[/b][hr]Declare @d smalldatetimeset @d = '1957/7/5'Select DATEDIFF(yy, @d, GETDATE()) - CASE WHEN DATEPART(m, @d) < DATEPART(m, GETDATE()) OR ( DATEPART(m, @d) = DATEPART(m, GETDATE()) AND DATEPART(d, @d) <= DATEPART(d, GETDATE()) ) THEN 0 ELSE 1 END [/quote]And your point is...?Fri, 04 Jul 2008 02:42:29 GMTDerek Dongrayhttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxDeclare @d smalldatetimeset @d = '1957/7/5'Select DATEDIFF(yy, @d, GETDATE()) - CASE WHEN DATEPART(m, @d) < DATEPART(m, GETDATE()) OR ( DATEPART(m, @d) = DATEPART(m, GETDATE()) AND DATEPART(d, @d) <= DATEPART(d, GETDATE()) ) THEN 0 ELSE 1 END Thu, 03 Jul 2008 19:31:35 GMTLuc Philiehttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxLove all the trick questions in the QOD stuff - I never even looked past the first one as even though it may not always give the correct birthdate in English/American terms, it could be adjusted to more readably do so by flooring the other values out of the year! Hand up, I got it wrong, but then IMO not a great question since the actual "correct" answer doesn't work either.Sat, 31 May 2008 06:52:13 GMTRobert Knightshttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspx[quote][b]john.arnott (4/10/2008)[/b][hr]Can someone who knows tell me why Datepart() would be preferrable? I like the distinct functions for readability.[/quote]Hi John,YEAR(...) is synonym for DATEPART(year, ...); similar for DAY and MONTH. From a technical point of view, there's no preference.The only thing left is readability. Some, like you, prefer the shorter versions because they are shorter and hence easier to understand. Others prefer DATEPART because you can also use that to expose dateparts that have no synonym (like, for instance, week, minute, quarter) and they consider it more consistent to use the same function in all cases.All in all, just a matter of personal preference. Though I'd recommend you to follow shop standards if there are any.Fri, 11 Apr 2008 01:14:34 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxGoodness, this one's gone on, hasn't it? And now I'm in it cuz of the row-size QOTD that had a naive age calculation thrown in. Anyway, before reading through all 19 pages, I coded this:[code]Declare @DOB smallDatetimeDeclare @Today smallDatetimeset @DoB = '02/29/2004'set @Today = '2/29/08'select @dob as Dob ,@today as Today ,year(@today)-year(@dob) - case when month(@today) < month(@dob) or (month(@today) = month(@dob) and day(@today) < day(@dob)) then 1 else 0 end as Age[/code]It turns out that it's pretty similar to some other solutions agreed to be correct, except that I used the functions "year()", "month()", and "day()" rather than "Datepart()".Can someone who knows tell me why Datepart() would be preferrable? I like the distinct functions for readability.Thu, 10 Apr 2008 18:26:33 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspx[quote][b]aarif.pd (4/10/2008)[/b][hr]the second one is correct. give me my point.[/quote]No, it is not. :)Thu, 10 Apr 2008 07:58:47 GMTRick Harkerhttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxthe second one is correct. give me my point.Thu, 10 Apr 2008 04:30:27 GMTaarif.pdhttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxThe second answer is the most accurate of the three; however, it does not account for leap years. The third answer would be the correct answer if the 1 and 0 were switched around.Tue, 01 Apr 2008 09:27:30 GMTephraimshttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxI agree with others here. Using my birth date the only one that gave me the correct answer was the second one and my birthday is 7 months away.I want my point! :)Mon, 31 Mar 2008 22:38:33 GMTNicole Bowmanhttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxHello,The "AND" in de case is clearly wrong because if the day of the BirthDay is inferior to the one obtain in the GETDATE() it will always return 0. CASE WHEN DATEPART(m, BirthDate) >= DATEPART(m, GETDATE()) [b]AND [/b]DATEPART(d, BirthDate) >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 ENDWed, 26 Mar 2008 04:56:13 GMTrui.romaohttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxIndeed, the 0 and the 1 should be reversed. However, if you plug in a birthdate that occurs on the current Month/Day, it is incorrect.Mon, 17 Mar 2008 14:11:03 GMTtim.mulherinhttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxI eliminated numbers 1 and 3 in my head since it didn't calculate my age correctly, and picked 2. Oh well. Anyway, it looks like number 3 should be:DATEDIFF(yy, @DateOfBirth, getdate()) - CASE WHEN DATEPART(m, @DateOfBirth) >= DATEPART(m, getdate()) AND DATEPART(d, @DateOfBirth) > DATEPART(d, getdate()) THEN 1 ELSE 0 END (changed the >= on the day comparison to > since most people count their birthday as the day they get older, and not the day after.)Sun, 16 Mar 2008 01:39:21 GMTarcainhttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxUsing my DOB, None of the 3 technically gave the correct answer.WHen I ran it my DOB was 4 days in the futureSetting my DOB to 2 months, 4 days in the future, this statement was the ONLY one that was correct:FLOOR(CONVERT(decimal(9, 2), DATEDIFF(d, @DateOfBirth, GETDATE())) / 365.0)Fri, 14 Mar 2008 10:34:08 GMTBrian Fergusonhttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxThe following formula could be more accurate(DATEDIFF(yy, @dob, getdate()) - CASE WHEN DATEPART(m, @dob) < DATEPART(m, getdate()) THEN 0 WHEN DATEPART(m, @dob) = DATEPART(m, getdate()) AND DATEPART(d, @dob) <= DATEPART(d, getdate()) THEN 0 ELSE 1 END) endHank CaoFri, 14 Mar 2008 10:18:36 GMTHank Caohttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxlol, as I see I am not the only one angry at the official answer....the following 2 statements both return 27 years old... select DATEDIFF(yy, '1/1/1980', GETDATE()) - CASE WHEN DATEPART(m, '1/1/1980') >= DATEPART(m, GETDATE()) AND DATEPART(d, '1/1/1980') >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 ENDselect DATEDIFF(yy, '11/11/1980', GETDATE()) - CASE WHEN DATEPART(m, '11/11/1980') >= DATEPART(m, GETDATE()) AND DATEPART(d, '11/11/1980') >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 ENDI want my point back!Wed, 12 Mar 2008 15:09:18 GMTHarveysburgerhttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxThe "real point" was that the author should have tested before posting. The topic was already full of "solutions" (many of them flawed), so why would I beat a dead horse?Wed, 12 Mar 2008 09:47:00 GMTDuncan A. McRaehttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxAll of them are incorrect. I think the correct age will be:SELECT DATEDIFF(yy, DateOfBirth, GETDATE()) - CASE WHEN DATEPART(m, DateOfBirth) < DATEPART(m, GETDATE()) OR (DATEPART(m, DateOfBirth) = DATEPART(m, GETDATE()) AND DATEPART(d, DateOfBirth) > DATEPART(d, GETDATE())) THEN 1 ELSE 0 END Wed, 12 Mar 2008 09:36:38 GMTMichael Abramovich-437553http://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspx[quote][b]ScottPletcher (3/11/2008)[/b][hr][quote]why does it make more sense for the birthday to be after their birthday than it does to have it be before? [/quote]Because then the exact same check that works for everyone else would work for them. No additional coding/checks required. Otherwise a large number of specific Feb 29/28 checks must be coded into age check ... yuck.And I quite agree with Hugo: my common sense tells me the birthday cannot occur on a date before it occurs.[/quote]Amusingly enough, my function was being critiqued for NOT having special clauses for Feb 29 birthdays. The code I used does not require special handlings for them. It uses native SQL functions (datediff and dateadd) on datetime data types.In order to get the results you want, 1 March instead of 28 Feb in non-leap years, you actually have to add more code to the solution.Tue, 11 Mar 2008 15:24:56 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxIts not only Taiwan, England and Wales also puts the legal birthday of leaplings as Feb 28. I think it has to do with trying to keep the birthday in February.Tue, 11 Mar 2008 12:52:34 GMTmartinohttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxHi Scott & GSquared,My common sense was actually not about when a birthdate is (I have at times celebrated by birthday several days before or after the day I was born + [i]x[/i] years), but at what moment one can be considered to be of a certain age.If someone is born in, for instance, 1992, on, let's say, March 11th - then nobody will disagree that his or her age in 2009 is 16 years on all days before March 11th, and 17 years on all days on or after March 11th. Likewise, if this person were born on February 28th, 1992, his or her age in 2009 would be 16 if month/day is less than February 28, and 17 if it's equal to or greater than February 28th.So logically, I would expect to be able to apply the same logic to someone born on February 29th 1992 - that in 20009, he or she will be 16 for as long as month/day is less than February 29th, and 17 as soon as month/day is equal to or greater than February 29th. The fact that the "equal to" in this comparison won't happen in 2009 should be immaterial to the logic.Obviously, at least the official Taiwan laws disagree with me. So I can't claim this reasoning to be "the truth". But I do still maintain that it IS the most logical way to calculate ages for leaplings, no matter what Taiwan thinks ;)Tue, 11 Mar 2008 09:51:15 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspx[quote]why does it make more sense for the birthday to be after their birthday than it does to have it be before? [/quote]Because then the exact same check that works for everyone else would work for them. No additional coding/checks required. Otherwise a large number of specific Feb 29/28 checks must be coded into age check ... yuck.And I quite agree with Hugo: my common sense tells me the birthday cannot occur on a date before it occurs.Tue, 11 Mar 2008 08:35:44 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspx[quote][b]ScottPletcher (3/10/2008)[/b][hr][quote] I don't know the laws for Los Angeles, CA, regarding this ... California law says, "on non-Leap Years, your birthday is official Feb 28"[/quote]Wouldn't L.A. fall under CA law?Where did you get that statement of CA law?All I can find for America says you're not legally older until March 1. (Although I certainly haven't found things for all states / territories, etc..)Feb 28 is just so impractical, because specific checks will have to be done for that. Moreover, no one else's birthday is ever before their birthday ... that's just too odd for me.[/quote]You may have misunderstood me. I was giving a "for example". I have no idea what California law is on the subject. That was the whole point of that paragraph. (Edit: you elipsed out the part of my writing where I said, "what if, for example...", which pretty clearly states that I'm not quoting some statute on the subject.)Per the web page I originally referenced, South Carolina and Maryland have different policies/problems with 29 Feb birthdates, so from that, I'm extrapolating that the US as a whole does not have a single cohesive policy/law/standard on the subject.On the subject of a person's birthday being before their birthday, why does it make more sense for the birthday to be after their birthday than it does to have it be before? Both arguments are forced by the fact that we're stuck using a Roman calendar that tries desperately and oddly to divide a 365.25-day year evenly into 12 months. Of course it has inconsistencies and oddities to it. It would be stranger still if it didn't.Tue, 11 Mar 2008 07:19:20 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspx[quote][b]GSquared (3/10/2008)[/b][hr]Lynn and Hugo:I took Leap Years into account, and even (to be totally anal retentive about it), the hour, minute, second, and number of milliseconds, on each time.Maybe I'm operating on the wrong standard here, but the people I have known who had Feb 29 birthdays (2 people, admitedly a very small sample), both celebrated their birthday on Feb 28 on non-leap years, not on March 1.[/quote]Hi GSquared,I'll gladly admit that I personally know no leaplings and that I had not bothered to look up any officiall info before. I just went by my personal common sense, which told me that one can not be considered a year older until MMDD of the current date is equal to or greater than MMDD of the birthdate.I also followed the link you posted. I found that site to be quite confusing, so I then went to Wikipedia (http://en.wikipedia.org/wiki/Leap_year#Birthdays). There, I found a clear description of at least one country (Taiwan) where leaplings are officially considered to be a year older on Feb 28 in common years. I didn't read on, since one counterexample always suffices to disprove any axiom.Since apparently the rules for leaplings differ per country (and apparently even per state in some countries), I can only agree that there is no single universally correct formula.As far as I see, your formula will indeed return correct results for all date combinations if the Taiwanese rules for leaplings apply. ;)Mon, 10 Mar 2008 16:40:20 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspx[quote] I don't know the laws for Los Angeles, CA, regarding this ... California law says, "on non-Leap Years, your birthday is official Feb 28"[/quote]Wouldn't L.A. fall under CA law?Where did you get that statement of CA law?All I can find for America says you're not legally older until March 1. (Although I certainly haven't found things for all states / territories, etc..)Feb 28 is just so impractical, because specific checks will have to be done for that. Moreover, no one else's birthday is ever before their birthday ... that's just too odd for me.Mon, 10 Mar 2008 15:26:42 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspx[quote][b]Lynn Pettis (3/10/2008)[/b][hr]The people you know who celebrate their birthdays on Feb 28 on non-leap years, doesn't mean the state or country they live in says they are another year older yet. The state or country may not recognize that they are another year older until March 1st. If you look at how many days into the year they are born (February 29th is the 60th day of the year), it makes sense that March 1st (the 60th day of the year in non leap years) is the point in time that they are another year older.Before you take this the other way, yes everyone born from March 1st onward could consider themselves a year older a day earlier during a leap year, but I don't think any of us old timers would care to do that. If you are born on February 29th, celebrate your birthday on February 28 or on March 1 (heck, celebrate it on both days), but I wouldn't say I was a year older until March 1st. Personal opinion, your opinion may differ and that's fine.:cool:[/quote]Totally correct. They may or may not have been "officially" older. In fact, it's quite possible they may have been both. I don't know the laws for Los Angeles, CA, regarding this, but neither do I know the laws of Sweden (one of the two was Swedish, or maybe Swiss, it's been 20 years and I know it was one of the two), nor of Seattle (the other one). If, for example, Swedish law says, "you're a year older on Feb 29 or Mar 1, whichever occurs first", and California law says, "on non-Leap Years, your birthday is official Feb 28", her passport might give her one age while her California ID card might have another. I don't know.That's the fun part of a question like this. It's like Phil Factor's [url]http://www.simple-talk.com/opinion/opinion-pieces/the-joy-of-nad/[/url] about the simplicity of a table for Name and Address. Starts out as something that should be solveable with simple common sense, and then explodes into a monstrous complexity that, all by itself, probably raises the stock prices for the companies that produce headache medication.Mon, 10 Mar 2008 14:55:03 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspx[quote][b]ScottPletcher (3/10/2008)[/b][hr]But even your own "timeanddate.com" link makes it clear that the official birthdate is Mar 1 in non-leap years:[quote] while others celebrate their birthday on March 1 because [b]they do not officially turn next age on February 28[/b]. [/quote][emphasis added][/quote]You may have missed the part in the very next paragraph that says that the rules vary depending on where they live."In some cases, their legal birthdays depend on the rules and regulations of where they live. Many countries make amendments for those born on leap days so they can be considered eligible for marriage, driving and other activities that require a legal age."Some officially age a year on the 1st, some on the 28th, depending on where they live. I'm not saying that my function takes that into account, because it obviously doesn't. I'm instead saying none of the functions in this thread take locality into account. That makes my assumption (Feb 28th), no more and no less valid than the ones that assume otherwise (Mar 1st).To truly build a function that would take all combinations of dates into account, it would have to account for prior calendars, as well as for Leap Year, and possibly even for differences in time zone (if you need that kind of precision).For example of how complex this simple question can get, if someone was born on 28 Feb in the year 5 BC, how old was he on 28 Feb 5 AD? Or, for a sort of trick question, if someone was born on 13 July 100 BC, and died on 15 March in 44 BC, how old was he? (Those are the dates given in Wikipedia for Julius Caesar's life span. But the month of "July" didn't exist until later, since it was named after him.) None of the functions given (including mine) can deal with either of those questions, since SQL Server's datetime data type doesn't correctly deal with BC dates and pre-Julian, Roman calendars.Also, none of the examples I'm seeing except mine take into account time on the birth certificate. This can matter in the case of, for example, a statuatory rape trial. Was the alleged victim 15 at the exact time of the alleged crime, or 16? It can make a difference.It all depends on the proposed purpose of the function. In the case of this particular Question of the Day, it was "which of these is the best", and there really should have been a "none of the above" option, since all three given options were wrong.In summary, NONE of the functions outlined in this thread yet are correct in all possible cases. Some of them are MORE correct than the answers given as options.Mon, 10 Mar 2008 14:46:33 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxThe people you know who celebrate their birthdays on Feb 28 on non-leap years, doesn't mean the state or country they live in says they are another year older yet. The state or country may not recognize that they are another year older until March 1st. If you look at how many days into the year they are born (February 29th is the 60th day of the year), it makes sense that March 1st (the 60th day of the year in non leap years) is the point in time that they are another year older.Before you take this the other way, yes everyone born from March 1st onward could consider themselves a year older a day earlier during a leap year, but I don't think any of us old timers would care to do that. If you are born on February 29th, celebrate your birthday on February 28 or on March 1 (heck, celebrate it on both days), but I wouldn't say I was a year older until March 1st. Personal opinion, your opinion may differ and that's fine.:cool:Mon, 10 Mar 2008 14:06:33 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxBut even your own "timeanddate.com" link makes it clear that the official birthdate is Mar 1 in non-leap years:[quote] while others celebrate their birthday on March 1 because [b]they do not officially turn next age on February 28[/b]. [/quote][emphasis added]Mon, 10 Mar 2008 14:05:33 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxHugo> "...(a daunting task by now, I know )..."lol, one of the reasons I love this site. We can all hash out ideas without egos getting in the way.Mon, 10 Mar 2008 13:54:47 GMTRick Harker