March 6, 2008 at 7:32 am
I also ran all three queries with my birthdate, and the first and third returned the wrong answer. However, the second one was correct.
March 6, 2008 at 7:37 am
That one works flawlessly.
DECLARE @DateOfBirth VARCHAR(10)
SET @DateOfBirth = '1960-03-07'
print YEAR(GETDATE()) - YEAR(@DateOfBirth) - CASE WHEN MONTH(GETDATE())*31 + DAY(GETDATE()) >= MONTH(@DateOfBirth)*31 + DAY(@DateOfBirth) THEN 0 ELSE 1 END
March 6, 2008 at 7:39 am
The formula is incorrect. If your birthday has occurred in the calendar year, the formula below returns 1 year less than it should. I know many people that would be happy if the formula was correct, but I am afraid they really are the age that DATEDIFF(yy, DateOfBirth, GETDATE()) gives.
DATEDIFF(yy, DateOfBirth, GETDATE()) - CASE WHEN DATEPART(m, DateOfBirth) >= DATEPART(m, GETDATE()) AND DATEPART(d, DateOfBirth) >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END
Q
Please take a number. Now serving emergency 1,203,894
March 6, 2008 at 7:44 am
Another point - the question specifies TINYINT, none of the queries return TINYINT. (1) is INT, (2) is DECIMAL, (3) is INT. Not only are the queries inaccurate, but none of them fit the criteria ... :crazy:
March 6, 2008 at 7:52 am
sorry the 3rd answer is not 100% correct as written
DECLARE @DateOfBirth DATETIME
SET @DateOfBirth='08/xx/1975' --commented out the day just because
SELECT DATEDIFF(yy, @DateOfBirth, GETDATE()) AS [first],
FLOOR(CONVERT(decimal(9, 2), DATEDIFF(d, @DateOfBirth, GETDATE())) / 365.0) AS [second],
DATEDIFF(yy, @DateOfBirth, GETDATE()) - CASE WHEN DATEPART(m, @DateOfBirth) >= DATEPART(m, GETDATE()) AND DATEPART(d, @DateOfBirth) >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END AS [third]
the result when it runs
firstsecondthird
333233
Don't age me before my time, only the second one was correct.
March 6, 2008 at 7:59 am
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:
DECLARE @BirthDate datetime
SET @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
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
March 6, 2008 at 8:00 am
Interesting fact to know about DATEDIFF -- seems like the wrong way to code it to me, but oh well . . .
Actually, none of the answers are right. Try:
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 0 ELSE 1 END
Pretty ugly. Why isn't there a Julian DATEPART?
March 6, 2008 at 8:02 am
The logic in the third statement does not properly relate the day of the month to the month of the year. It should read:
select DATEDIFF(yy, BirthDate, GETDATE())
- CASE
WHEN DATEPART(m, BirthDate) >= DATEPART(m, GETDATE()) THEN 1
WHEN DATEPART(m, BirthDate) = DATEPART(m, GETDATE())
AND DATEPART(d, BirthDate) >= DATEPART(d, GETDATE())
THEN 1
ELSE 0
END
March 6, 2008 at 8:15 am
Aye, your fourth statement is what works correctly, Peter.
March 6, 2008 at 8:24 am
I ran all three queries against my own birth date. The first and the third ones returned the wrong value. The second one returned the correct result.
Please fix this and re-score everyone who turned in the correct answer.
By the way, dosen't anyone there check the questions for accuracy before you put them out?
-- WP
March 6, 2008 at 8:28 am
The second query would give a reasonable answer if you use 365.25 instead of 365.
The third query will not work if DOB is, for example, Feb 7 1987 and the month is March (now) but the day is 6 (today). You need *nested* loops -- check for month, then check for day only if the month is equal. (Once current month is greater than birth month, you do not need to check the day.)
I know a lot about this because I created the same problem for my S.Q.L. students! Plus I had someone's GETAGE user-defined function that worked as I described.
March 6, 2008 at 8:30 am
The third query is wrong:
DATEDIFF(yy, DateOfBirth, GETDATE()) - CASE WHEN DATEPART(m, DateOfBirth) >= DATEPART(m, GETDATE()) AND DATEPART(d, DateOfBirth) >= DATEPART(d, GETDATE()) THEN 0 ELSE 1 END
It instead 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
Note the requirement that 1 should be subtracted from the following if the person's birthday has not yet been attained as of GETDATE(). Also, to berify the person's birthday has not yet been attained, we need to use the ">" sign instead of the ">=" sign.
March 6, 2008 at 8:35 am
declare @DoB datetime
declare @Today datetime
declare @YearsOld tinyint
set @DoB = '3/5/1985 6pm'
-- Tests.
set @Today = '3/5/2008 2am'
set @Today = '3/5/2008 5:59pm'
set @Today = '3/5/2008 6:00pm'
--set @Today = '3/5/2008 6:01pm'
--set @Today = '3/5/2008 7pm'
--set @Today = getdate()
;WITH-- Jeff Moden's CTE replacement for number table.
cTally AS(
SELECT TOP (2400) -- 200 years of months.
ROW_NUMBER() OVER (ORDER BY t1.ID) AS Number
FROM master.sys.SysColumns t1
CROSS JOIN master.sys.SysColumns t2
)
select
@YearsOld = count( * ) / 12
from cTally
where DateAdd( month, Number, @DoB ) <= @Today
select @YearsOld
March 6, 2008 at 8:36 am
I have to agree the third statement just produces the wrong value. The 2nd is closest although it should divide by 365.25 rather than 365 but the difference between your real age and the calculated one is negligible enough not to produce an error (in terms of years) even if you are several hundreds of years old. The second while not being strictly exact is the only one that produces the correct age.
Francis
March 6, 2008 at 8:39 am
That's a bug in sqlserver.
Datediff(m,@birthdate,getdate()) subtracts dates and returns months.
Datediff(d,@birthdate,getdate()) subtracts dates and returns days.
Datediff(yy,@birthdate,getdate()) subtracts year parts, contrary to expectations and documentation wich says "Returns the number of intervals between two dates" - so it should return number of years between dates.
BOL also says:
When comparing December 31 to January 1 of the immediately succeeding year, DateDiff for Year ("yyyy") returns 1 even though only a day has elapsed.
From this you can deduce it subtracts year parts and not calculate year part of date difference.
So ALL answers are wrong.
A better variation for #2 would be FLOOR(CONVERT(decimal(9, 2), DATEDIFF(d, DateOfBirth, GETDATE())) / 365.25)
Correct answer #3 would be
DATEDIFF(yy, DateOfBirth, GETDATE()) - CASE WHEN DATEPART(m, DateOfBirth) = DATEPART(m, GETDATE()) AND DATEPART(d, DateOfBirth) >= DATEPART(d, GETDATE()) or DATEPART(m, DateOfBirth) > DATEPART(m, GETDATE()) THEN 1 ELSE 0 END
BTW, 365.25 is a good approximation of year's length in days for human lifetime, while more accurate would be 365.2475.
Viewing 15 posts - 16 through 30 (of 189 total)
You must be logged in to reply to this topic. Login to reply