Difference between two dates - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Difference between two dates

Author

Topic

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-05-20 : 10:31:19

I'm looking to write a function that will return the difference of two dates up to years. Sort of like "1 yr 3 mo 2 days 6 hrs 58 min 43.123 secs". I've tried a few things by first deriving the number of milliseconds between the two dates but ran into overflow issues quickly. Any tips?

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-05-20 : 10:46:31

If you want it down to milliseconds, you'll have to do it in several parts, using logic of your own. If seconds is enough, you can do it directly if the difference is less than 68 years.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-05-20 : 10:57:07

We currently have a function someone else coded that looks something like this:

CREATE FUNCTION dbo.FormatElapsedTime (@ElapsedTime DATETIME, @Method NVARCHAR(15))
RETURNS NVARCHAR(200)
AS
BEGIN
DECLARE @Result NVARCHAR(200)
DECLARE @Days INT
DECLARE @Hours INT
DECLARE @Minutes INT
DECLARE @Seconds INT
DECLARE @MilliSeconds INT

SET @Days = CAST(@ElapsedTime AS INT)
SET @ElapsedTime = @ElapsedTime - @Days
SET @Hours = DATEPART(hh, @ElapsedTime)
SET @Minutes = DATEPART(mi, @ElapsedTime)
SET @Seconds = DATEPART(ss, @ElapsedTime)
SET @MilliSeconds = DATEPART(ms, @ElapsedTime)
...and so on (the rest is just formatting based on different @Method parameters.

The only problem with this is it's not always 100% accurate. The calling procedure passes in @ElapsedTime sort of like so: @EndDate - @StartDate. However, it doesn't deal with negative numbers very well (returns bad results). I figured to get it to work in either direction correctly I'd break it down into the lowest common denominator (milliseconds) and go from there.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-05-21 : 13:07:08

I've figured out the main problem I'm having is related to how the MS date functions only count boundaries, not actual periods of time. I've searched high and low for quick samples I could put together to match what I need but no luck. Does anyone here have any other references that might be beneficial to myself?

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-05-21 : 13:20:18

This might help. It doesn't go down to MS,but I think you can make it do that easily. This was posted as a solution to one of my problems form abotu a year ago or so.


DECLARE @Diff int
DECLARE @DateDiff VARCHAR(100)

SELECT @diff=DateDiff(ss, '5/20/2003', GETDATE())

SELECT @DateDiff = 	CAST(@diff/86400 as varchar) + ' days ' + 
			CAST((@diff%86400)/3600 as varchar) + ' hours ' + 
			CAST((@diff%3600)/60 as varchar) + ' minutes ' + 
			CAST(@diff%60 as varchar) + ' seconds'

select @DateDiff

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Edited by - michaelp on 05/21/2003 13:23:09

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-05-21 : 13:35:39

Hot damn. And I was doing it the hard way! Thanks for saving me some work. Now I have some nice clean, consolidated code to compare to my screwed up way.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-05-21 : 17:40:50

Actually, trying to implement years causes issues with days around leap years. I even tried the 365.25 trick and it doesn't work. I think I found another solution that will work 100% of the time with the range of values that a DATETIME datatype will store, avoiding any ugly overflow errors. It's a little bit more coding but it seems to work like a charm. I'll post it when I'm done tomorrow.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-05-22 : 13:17:48

Ok, my solution is as follows. Not the best code but it works and is pretty simplistic:

DECLARE @TempDate DATETIME
DECLARE @Years INT
DECLARE @Months INT
DECLARE @Days INT
DECLARE @Hours INT
DECLARE @Minutes INT
DECLARE @Seconds INT
DECLARE @Milliseconds INT

DECLARE @Result NVARCHAR(200)

--Initialize @TempDate and other variables.
SET @TempDate = @StartDate
SET @Years = 0
SET @Months = 0
SET @Days = 0
SET @Hours = 0
SET @Minutes = 0
SET @Seconds = 0
SET @Milliseconds = 0

--Derive the year difference.
SET @Years = DATEDIFF(YEAR, @TempDate, @EndDate)
SET @TempDate = DATEADD(YEAR, @Years, @TempDate)
IF @TempDate > @EndDate
BEGIN
SET @Years = @Years - 1
SET @TempDate = DATEADD(YEAR, -1, @TempDate)
END

--Derive the number of months.
SET @Months = DATEDIFF(MONTH, @TempDate, @EndDate)
SET @TempDate = DATEADD(MONTH, @Months, @TempDate)
IF @TempDate > @EndDate
BEGIN
SET @Months = @Months - 1
SET @TempDate = DATEADD(MONTH, -1, @TempDate)
END

--Derive the number of days.
SET @Days = DATEDIFF(DAY, @TempDate, @EndDate)
SET @TempDate = DATEADD(DAY, @Days, @TempDate)
IF @TempDate > @EndDate
BEGIN
SET @Days = @Days - 1
SET @TempDate = DATEADD(DAY, -1, @TempDate)
END

--Derive the number of hours.
SET @Hours = DATEDIFF(HOUR, @TempDate, @EndDate)
SET @TempDate = DATEADD(HOUR, @Hours, @TempDate)
IF @TempDate > @EndDate
BEGIN
SET @Hours = @Hours - 1
SET @TempDate = DATEADD(HOUR, -1, @TempDate)
END

--Derive the number of minutes.
SET @Minutes = DATEDIFF(MINUTE, @TempDate, @EndDate)
SET @TempDate = DATEADD(MINUTE, @Minutes, @TempDate)
IF @TempDate > @EndDate
BEGIN
SET @Minutes = @Minutes - 1
SET @TempDate = DATEADD(MINUTE, -1, @TempDate)
END

--Derive the number of seconds.
SET @Seconds = DATEDIFF(SECOND, @TempDate, @EndDate)
SET @TempDate = DATEADD(SECOND, @Seconds, @TempDate)
IF @TempDate > @EndDate
BEGIN
SET @Seconds = @Seconds - 1
SET @TempDate = DATEADD(SECOND, -1, @TempDate)
END

--Derive the number of milliseconds (should be the only value left w/o worrying about flowing into seconds).
SET @Milliseconds = DATEDIFF(MILLISECOND, @TempDate, @EndDate)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources