Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sherpa99
Starting Member
20 Posts |
 Posted - 2005-04-11 : 14:49:38
|
| It appears that the datediff function rounds-up when in "Year" mode -- how can that be? what am I doing wrong?My examples---------------------this works ... correct answer should be 40SELECT DATEDIFF(year, '1/1/1965', getdate()) AS no_of_yearsthis is wrong, correct answer should be 39 -- but it returns 40???SELECT DATEDIFF(year, '12/31/1965', getdate()) AS no_of_yearsAny ideas? |
|
|
PW
Yak Posting Veteran
95 Posts |
Posted - 2005-04-11 : 14:55:34
|
| BOL on DateDiff() states:"Returns the number of date and time boundaries crossed between two specified dates. "The number of year boundaries crossed is the same in both your exampels, therefore DateDiff() is behaving according to specifications. |
 |
|
|
rfrancisco
Yak Posting Veteran
95 Posts |
Posted - 2005-04-11 : 14:55:54
|
| DATEDIFF(YEAR, date1, date2) simply returns the difference between the year without considering the date. The result you're getting is correct.You cannot use datediff(year) to get the age of a person given the birthdate. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-04-11 : 14:59:47
|
| I guess you're looking for something like this:Select datediff(day,'12/31/1965', getdate()) / 365orSelect datediff(day,'12/31/1965', getdate()) / 365.0Be One with the OptimizerTG |
|
|
|
sherpa99
Starting Member
20 Posts |
Posted - 2005-04-11 : 15:35:08
|
| TG -- that's the ticket -- thanks everyone! |
|
|
|
jkncrew
Starting Member
1 Post |
Posted - 2010-09-24 : 15:06:16
|
| How do you get around leap year? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
|
|
Datediff rounds-up for years?