Calculating datetime.

Question

Post reply

Calculating datetime.

ruffindmc

SSC-Addicted

Points: 424
More actions
May 19, 2011 at 2:27 pm

#237874

I am having trouble with calculating datetimes. Its more of a rounding issue with hours/minutes. I am coming up with my times being a minute off another source used for comparison. I know it is related to including the seconds, but I am having trouble with the calculation.
Start time = '4/29/2011 11:23:44 AM'
End TIme = '4/29/2011 1:27:31 PM'
My source comes up with 2:04 and I come up with 2:03 hours. The user I am working with is concerned. Any suggestions as to how I can get the seconds included?
Thanks.
DMR

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ken Jr. SSCommitted Points: 1842 More actions · Answer 1

Found this: http://sql-server-performance.com/Community/forums/p/27683/149404.aspx

declare @start datetime, @end datetime, @duration varchar(36),

@hours varchar(30), @minutes varchar(2), @seconds varchar(30)

set @start = '4/29/2011 11:23:44 AM'

set @end = '4/29/2011 1:27:31 PM'

set @seconds = abs(datediff(second, @start, @end))

set @hours = @seconds / 3600

set @minutes = (@seconds - (@hours * 3600)) / 60

set @seconds = (@seconds - (@hours * 3600) - (@minutes * 60))

if cast(@hours as int) < 10 set @hours = '0' + @hours

if cast(@minutes as int) < 10 set @minutes = '0' + @minutes

if cast(@seconds as int) < 10 set @seconds = '0' + @seconds

set @duration = @hours + ':' + @minutes + ':' + @seconds

select @duration

ruffindmc SSC-Addicted Points: 424 More actions · Answer 2

Thank you. This works well it I disply seconds. The problem of rounding is still an issue. I do not want to have seconds and when I remove them, it is still short by a minute. So there is still a 1 minute difference.

DMR

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

Here is a step by step possible solution for you to walk through and combine statements when you understand them and of course want to.

DECLARE @St AS Datetime

DECLARE @et AS Datetime

DECLARE @hr AS INT

DECLARE @min-2 AS INT

DECLARE @Xtra AS INT

DECLARE @Dur AS VARCHAR(10)

SET @St = '4/29/2011 11:23:44 AM'

SET @et = '4/29/2011 1:27:31 PM'

--==SELECT DATEDIFF(ss,@St,@Et) AS 'Seconds'

SET @hr = DATEDIFF(ss,@St,@Et)/3600

--==SELECT @hr AS 'hours' --only to show for testing

SET @Dur = CAST(@hr AS VARCHAR(2)) + ':'

--==SELECT @Dur AS 'Duration' --only to show for testing

SET @Xtra = (DATEDIFF(ss,@St,@Et)) - (@hr * 3600)

--==SELECT @Xtra AS 'Extra seconds' --only to show for testing

SET @min-2 = @Xtra/60--only to show for testing

--==SELECT @min-2 AS 'Minutes' --only to show for testing

SET @Xtra = @Xtra - (@Min * 60)

--==SELECT @Xtra AS 'Extra' --only to show for testing

--== this could be change to > 0 to ALWAYS round upward

If @Xtra > 30

SET @min-2 = @min-2 + 1

SET @Dur = @Dur + RIGHT('0' + CAST(@Min AS VARCHAR(2)),2)

SELECT @Dur AS 'Duration'

Result:

Duration

2:04

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

WayneS SSC Guru Points: 95460 More actions · Answer 4

ruffindmc (5/19/2011)
I am having trouble with calculating datetimes. Its more of a rounding issue with hours/minutes. I am coming up with my times being a minute off another source used for comparison. I know it is related to including the seconds, but I am having trouble with the calculation.
Start time = '4/29/2011 11:23:44 AM'
End TIme = '4/29/2011 1:27:31 PM'
My source comes up with 2:04 and I come up with 2:03 hours. The user I am working with is concerned. Any suggestions as to how I can get the seconds included?
Thanks.
DMR

So your source is comparing just the minutes (2:04), while you're comparing with the seconds. So, strip the seconds off, and do your comparison:

DECLARE @Start DATETIME = '4/29/2011 11:23:44 AM',

@End DATETIME = '4/29/2011 1:27:31 PM',

@Comp DATETIME = '20010101';

WITH cteDates AS

(

-- strip the minutes off of the supplied dates

SELECT StartTime = DATEADD(MINUTE, DATEDIFF(MINUTE, @Comp, @Start), @Comp),

EndTime = DATEADD(MINUTE, DATEDIFF(MINUTE, @Comp, @End), @Comp)

), cteMinutes AS

(

-- get the number of minutes between these dates

SELECT StartTime,

EndTime,

[Minutes] = DATEDIFF(MINUTE, StartTime, EndTime)

FROM cteDates

)

-- break down the minutes into hours, and the remainder of the minutes

SELECT StartTime,

EndTime,

Elapsed = CONVERT(VARCHAR(10), [Minutes] / 60) + ':' + RIGHT('00' + CONVERT(VARCHAR(10), [Minutes] % 60), 2)

FROM cteMinutes;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2