March 9, 2008 at 4:58 am
From what I've read here on the subject, Henk Schreij got it right with:
SELECT (0+CONVERT(CHAR(8),GETDATE(), 112) - CONVERT(CHAR(8), @d, 112))/10000
March 9, 2008 at 12:26 pm
Hugo,
I stand corrected. I was shooting from the hip without doing enough research...
March 10, 2008 at 2:46 am
Second query gives more correct answers. It also calculates incorrect result 1-15 days after today depending on the age.
For instance a person who was born on "19720315" gets incorrect answer for today "20080310".
March 10, 2008 at 7:04 am
Without extensively testing - I haven't checked whether int or round is the correct function below; however, at the very least, the two functions below will give you the correct year.
To make it work, transalate the birthdate to a JulianDay then run the reverse JulianDay routine to get the correct birthdate (including, of course, the correct YEAR). To get the age of a person, convert dates to JulianDay and subtract the difference, then divide by 365.2422.
This routine works just as well for Julius Caesar and Plato as well as it works for someone born this morning. 1753 was a tough year because we lost 10 days that year. Try a few dates.
The word "BEST" in this question was not used aptly. It might have been worded "Which of these methods is the worst?" or "What should be done to correct these methods?". It would have been more accurate. The misuse of the word BEST is, well, how can I put it nicely?
select dbo.ReverseJuilianDay(dbo.JulianDay('2','14','1753'))
-- =============================================
-- Author: JRL
-- Create date: 12/27/07
-- Description: Return Reverse Julian day [for any date]
-- Gives you about a 10000 year window on dates
-- =============================================
CREATE FUNCTION [dbo].[ReverseJuilianDay]
(
@JulianDay Numeric
)
RETURNS varchar(12)
AS
BEGIN
--2434663.5
--Select dbo.ReverseJuilianDay(2434663.5)
Declare @Century decimal(20,8), @Month smallint,@day smallint,@year smallint,@dblStore Numeric,@Months varchar(36),@Return varchar(12)
Set @JulianDay=@JulianDay+.499999999999999999994
Set @JulianDay=Convert(int,@JulianDay)
Set @dblStore=@JulianDay-1721119 --713546
Set @dblstore=4*@dblStore-1 --2854183
Set @Year=@dblstore/146097--19
Set @dblStore=@dblStore-(146097*@year) --78340
Set @day=@dblstore/4
Set @dblStore = convert(int,(4.0 * @Day + 3.0) / 1461.0)
Set @Day = 4.0 * @Day + 3.0 - 1461.0 * @dblStore
Set @Day = convert(int,(@Day + 4) / 4)
Set @Month = Convert(int,(5 * @Day - 3) / 153)
Set @Day = 5 * @Day - 3 - 153 * @Month
Set @Day = Convert(int,(@Day + 5) / 5)
Set @Year = 100 * @Year + @dblStore
If @Month < 10
Set @Month = @Month + 3
Else
BEGIN
Set @Month = @Month - 9
Set @Year = @Year + 1
END
--Day$ = RIGHT$("00" + MID$(STR$(Day%), 2), 2)
--Month$ = RIGHT$("00" + MID$(STR$(@Month), 2), 2)
--@Return = Month$ + "-" + Day$ + "-" + RIGHT$(STR$(@Year), 4)
Set @Months = 'JanFebMarAprMayJunJulAugSepOctNovDec'
Set @Return = substring(@Months, @Month * 3 - 2, 3) + ' '
Set @Return = @Return + Right(Convert(varchar(2),@Day), 2) + ', ' + Right(Convert(varchar(4),@Year), 4)
-- Return the result of the function
RETURN @Return
END
--======================
-- =============================================
-- Author: JRL
-- Create date: 12/27/07
-- Description: Return Julian day for any date AD or BC
-- Roughly going back 5000 years or so
-- =============================================
CREATE FUNCTION [dbo].[JuilianDay]
(
-- Add the parameters for the function here
@Month decimal (2,0),@day decimal (2,0),@year decimal (4,0)
)
RETURNS decimal(14,1)
AS
BEGIN
Declare @Century decimal(20,8), @JulianDay decimal(20,8)
Set @Century=12.0*(@Year+4800)+@Month-3
Set @JulianDay=Convert(int,@Century/48)+Convert(int,(365*@Century+2 *(@Century-12*Convert(int,@Century/12))+7)/12)+@day-32083
If @JulianDay>2299170.0
BEGIN
Set @JulianDay=@JulianDay+Convert(int,@Century/4800)-Convert(int,@Century/1200)+38
END
--Julian day = 12 hrs GMT so now remove half a day to get GMT
Set @JulianDay=@JulianDay -.5
-- Return the result of the function
RETURN @JulianDay
END
Jamie
March 10, 2008 at 8:36 am
Hi Jamie,
Interesting approach - but unfortunately, it shares the problem also exhibited by several other suggestions posted here, that it won't work correctly in non-leap years.
For instance, the calculated difference between the julian date for April 1st 2006 and April 1st 2007 is 365 days (which is correct) - dividing that by 365.2425 results in 0.9999some, which after converting to smallint is rounded down to 0 years (which is incorrect).
No solution that tries to determine age by calculating number of days and then dividing by average year length will ever work - because some years are a day longer than others, there will always be borderline cases where this approach results in incorrect results.
March 10, 2008 at 8:41 am
I think it may work if the int function is changed to round. I took this from VB code which does work. I confused the cint function in VB with the Floor/Ceiling and Round function. It works differently there. The actual logic is correct and tested but just not in SQL.
Jamie
March 10, 2008 at 8:53 am
I would like to point out that my little proc has not been shown to be wrong... 🙂
(I was hoping for some sort of feedback.)
March 10, 2008 at 9:08 am
Hugo,
http://www.sizes.com/time/cal_gregorian.htm
Try changing the Int to Round. Note that the calculation takes into account the factors you mentioned. I just put this one out there (with int instead of round) because the code has been tested in Visual Basic with perfect accuracy for me for more than twenty years.
To be more specific - the vb cint function does not behave the way the SQL INT function works. In VB, cint(.5)=1 and cint(1.5)=2 and cint(.9999)=1 SQL does not behave this way but I believe you can round(.5) to be equal to 1. Ditto on your .9999some.
Jamie
Jamie
March 10, 2008 at 9:15 am
Rick,
I don't know how to search the posts and there are too many to find yours. Can you repost?
Jamie
Jamie
March 10, 2008 at 9:22 am
Sure, no problem! I see it on page 3.
http://qa.sqlservercentral.com/Forums/Topic464861-1181-3.aspx
March 10, 2008 at 9:47 am
Jamie Longstreet (3/10/2008)
Hugo,http://www.sizes.com/time/cal_gregorian.htm
Try changing the Int to Round. Note that the calculation takes into account the factors you mentioned. I just put this one out there (with int instead of round) because the code has been tested in Visual Basic with perfect accuracy for me for more than twenty years.
To be more specific - the vb cint function does not behave the way the SQL INT function works. In VB, cint(.5)=1 and cint(1.5)=2 and cint(.9999)=1 SQL does not behave this way but I believe you can round(.5) to be equal to 1. Ditto on your .9999some.
Jamie
Hi Jamie,
Thanks for the link, but I know how the Gregorian calendar works 🙂
Changing int to round will introduce much more errors, since anything over approximately half a year will be rounded up (so when you're 37 years and 9 months old, the calculation will return 38).
The reason why your approach, and all of the variations on this theme, will not work is simple. A time span of 365 days may be exactly one year, but it may also be one day short of a year. That depends on whether a leap day is included in that time span. So no matter what you divide 365 by and how you round or truncate the results, you are bound to get it wrong at least some of the time!
I realize my previous explanation was a bit unclear; I hope this one does a good job of explaining the problem 😉
March 10, 2008 at 9:52 am
The pseudocode is correct, the implementation isn't. The correct answer is:
CAST(DATEDIFF(yy, @DateOfBirth, GETDATE()) - CASE WHEN RIGHT(CONVERT(CHAR(8), @DateOfBirth, 112), 4) <= RIGHT(CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112), 4) THEN 0 ELSE 1 END AS TINYINT)
March 10, 2008 at 10:00 am
Rick Harker (3/10/2008)
I would like to point out that my little proc has not been shown to be wrong... 🙂(I was hoping for some sort of feedback.)
Sorry, Rick - I missed it (so many solutions posted here, so little time...)
Okay, here are my thoughts:
1) The only erroneous result I could see is when someone is born on a leap day - your code will consider this person to be 1 year old on February 28 the next year, whereas I'd find March 1st to be more logical.
2) On a server with a case sensitive collation, "Master" will produce an error. Should have been "master".
3) Ages over 200 years will be calculated incorrectly.
4) It's terribly inefficient. For each date, 2400 rows have to be processed and 2400 calculations have to be performed. Imagine calculating age for all 100 million (?) rows in the US tax payers registration...
5) The above can be somewhat compensated by calculating with years instead of months. With the approach you chose, I fail to see why you would want to work with months.
Overall, it's better than some, but not perfect (incorrect results for 1 in 1,500 people on three days every four years) - and much to slow to be ever called "best".
March 10, 2008 at 10:03 am
I like the fact that Rick used the CTE. Interesting approach Rick
Jamie
Jamie
March 10, 2008 at 10:11 am
Hugo,
Not sure what you mean but I took the date you implied (37 years and 9 months ago) and ran the routine and came up with 37 and 3/4 years which is correct.
select (dbo.JulianDay('3','10','2008')-dbo.JulianDay('6','10','1970'))
select 13788.0/365.2422
Jamie
Jamie
Viewing 15 posts - 121 through 135 (of 189 total)
You must be logged in to reply to this topic. Login to reply