DateTime calculations

sudarshan73 · March 15th, 2005, 02:51 PM

Hi,i would appreciate your help on getting the time difference in hrs, min, seconds,microsec between two datetime values
for e.g difference ('2004-12-31 23:21:58.727',
'2004-12-31 23:25:22.410')
Please help
Thank in advance
suddy

shahchi1 · March 15th, 2005, 03:08 PM

USE DATEDIFF Function

I think it is supported by both Access and SQL server

DATEDIFF(diffrence, startdate, enddate)

SELECT DATEDIFF(mi,'2004-12-31 23:21:58.727','2004-12-31 23:25:22.410') As MinuteDifference

SELECT DATEDIFF(ss,'2004-12-31 23:21:58.727','2004-12-31 23:25:22.410') As SecondsDiffrence

sudarshan73 · March 15th, 2005, 03:21 PM

Hi shahchi1
Thanks, but i wanted the total time difference for e.g in this format
(HH:mi:ss:ms)
('2004-12-31 23:21:58.727',
'2004-12-31 23:25:22.410')
Answer = (0:3:23:683)
Your help would great.please

shahchi1 · March 15th, 2005, 04:41 PM

If you are using T-SQL

SELECT RIGHT(CONVERT(varchar(25),CONVERT(datetime, '2004-12-31 23:25:22.410') - CONVERT(datetime, '2004-12-31 23:21:58.727'),121)
,12)

sudarshan73 · March 15th, 2005, 05:00 PM

Hi shahchi1
i tried the following on a different date value but i get a wrong answer/value

SELECT RIGHT(CONVERT(varchar(25),CONVERT(datetime, '2004-10-12 14:30:04:220') - CONVERT(datetime, '2004-10-12 14:45:04:209'),121)
,12)
answer i get is 23:45:00.010

shahchi1 · March 15th, 2005, 05:07 PM

Solution I posted works only if the first date is greater than your second date...
(I mean quantitatively) if you want more robust solution then create a UDF (user defined function, pass two dates and get the result

sudarshan73 · March 15th, 2005, 07:14 PM

Hi shahchi1
Thanks alot , i could just reverse the dates, in which case the end date is always greater then start date and it works fine.
Thanks a million for superb solutions.you are great.

Quote:

quote:Originally posted by shahchi1
Solution I posted works only if the first date is greater than your second date...
(I mean quantitatively) if you want more robust solution then create a UDF (user defined function, pass two dates and get the result

dug · April 7th, 2005, 07:52 AM

I believe this will do what you want, unless the values are from two different days.

declare @StartTime datetime,
@EndTime datetime
select @StartTime = getdate()
exec My_Stored_Procedure
select @EndTime = getdate()
select @EndTime - @StartTime

rgerald · April 7th, 2005, 09:07 AM

Like the above with the following:

Code:

DECLARE @StartTime datetime
DECLARE @EndTime datetime
DECLARE @DiffMinutes decimal (12, 2)
DECLARE @vDiffMinutes varchar(12)

SET @StartTime = GetDate()
PRINT 'Started at: ' + CAST(@StartTime AS varchar(25))

 -- Put your stored procedure or other code here. -- 

SET @EndTime = GetDate()
PRINT 'Started at: ' + CAST(@StartTime AS varchar(25))
PRINT 'Ended at:   ' + CAST(@EndTime AS varchar(25))
SET @DiffMinutes = CAST(CAST(DATEDIFF(ms, @StartTime, @EndTime) AS decimal(14, 2)) / 60000 AS decimal(12, 2))
SET @vDiffMinutes = CAST(@DiffMinutes AS varchar(12))
PRINT 'Run time:   ' + @vDiffMinutes + ' minute(s)'

Rand