Accurate datediff by years
-
I tried performing datediff on two strings converted into datetimes and found that it simply subtracts the years of the two dates as opposed to taking actually calculating the difference. For example,
datediff( yy, convert(datetime, '2004-12-31', 120), convert(datetime, '2005-01-01', 120))
and
datediff( yy, convert(datetime, '2004-01-01', 120), convert(datetime, '2005-12-31', 120))
both return 1. It does not round up or down, it just takes the year parts and subtracts them. Is there an accurate way to calculate the date difference and takes the floor of the difference so that the first statement would result in 0 and the second would result in 1?
Thanks in advance!
-
you can always count the number of minutes bewteen the dates and divide by (60*24*365.256)
-
OR...
You could figure out the MONTH difference and divide by 12 ie.
SELECT datediff(MONTH, convert(datetime, '2004-12-31', 120), convert(datetime, '2005-01-01', 120)) / 12
SELECT datediff(MONTH, convert(datetime, '2004-01-01', 120), convert(datetime, '2005-12-31', 120)) / 12
Good Hunting!AJ Ahrens
webmaster@kritter.net -
It's all a matter of required precision >>
SELECT datediff(MONTH, convert(datetime, '2004-12-31', 120), convert(datetime, '2005-01-01', 120)) / 12.0 as W_Months, datediff(MINUTE, convert(datetime, '2004-12-31', 120), convert(datetime, '2005-01-01', 120)) / (60.0 * 24*365.256) as W_Minutes
UNION ALL
SELECT datediff(MONTH, convert(datetime, '2004-01-01', 120), convert(datetime, '2005-12-31', 120)) / 12.0 as W_Months, datediff(MINUTE, convert(datetime, '2004-01-01', 120), convert(datetime, '2005-12-31', 120)) / (60.0 * 24*365.256) as W_Minutes
/*
W_Months W_Minutes
------------------- ------------------------------
.083333 .00273780581290
1.916666 1.99859824342379
*/
-
The problem isn't that DATEDIFF subtracts years... it's that it counts the number of times the date changes from 12/31 to 01/01. Subtle difference but very important using other date part operands for DATEDIFF.
If you want a year based on 365.25 days, then just subtract the earlier date from the later date and divide the difference by 365.25 (or just 365.0, be sure to include the ".0" or you may get integer math rounding).
SELECT (@EndDate-@StartDate)/365.25
...OR...
SELECT (@EndDate-@StartDate)/365.0
You could get a bit more sophisticated by bouncing the dates off the base date of 01/01/1900 using DATEDIFF but, unless you really need accuracy to the day, that would be overkill.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
maybe this can be used ...
-- how to determin SQLServer Uptime
-- Tracking Uptime by Brian Moran http://www.winnetmag.com/SQLServer/Article/ArticleID/38042/SQLServer_38042.html
--
SELECT @@servername as ServerName, datediff(mi, login_time, getdate()) as SQLServer_UpTime_Minutes
FROM master..sysprocesses WHERE spid = 1
go
-- my own tests
SELECT @@servername as ServerName, Dateadd(ss, SQLServer_UpTime_Seconds * (-1), getdate() ) as StartUp_DateTime
from (
SELECT datediff(ss, login_time, getdate()) as SQLServer_UpTime_Seconds
FROM master..sysprocesses
WHERE spid = 1
) a
go
SELECT @@servername as ServerName, getdate() - login_time as SQLServer_UpDateTime_1900_01_01
FROM master..sysprocesses
WHERE spid = 1
go
SELECT @@servername as ServerName, Year( SQLServer_UpTime) - 1900 - case when month( SQLServer_UpTime) - 1 - case when day( SQLServer_UpTime) - 1 < 0 then 1 else 0 end < 0 then 1 else 0 end as Years
, month( SQLServer_UpTime) - 1 - case when day( SQLServer_UpTime) - 1 < 0 then 1 else 0 end as Months
, day( SQLServer_UpTime) - 1 as Days
, substring(convert(varchar(25), SQLServer_UpTime,121),12,8) as Timepart
from (
SELECT getdate() - login_time as SQLServer_UpTime -- opgepast start vanaf 1900-01-01
FROM master..sysprocesses
WHERE spid = 1
) a
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply