Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 15th, 2005, 02:51 PM
Authorized User
 
Join Date: Mar 2005
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default DateTime calculations

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

 
Old March 15th, 2005, 03:08 PM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 303
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old March 15th, 2005, 03:21 PM
Authorized User
 
Join Date: Mar 2005
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



 
Old March 15th, 2005, 04:41 PM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 303
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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)
 
Old March 15th, 2005, 05:00 PM
Authorized User
 
Join Date: Mar 2005
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old March 15th, 2005, 05:07 PM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 303
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old March 15th, 2005, 07:14 PM
Authorized User
 
Join Date: Mar 2005
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old April 7th, 2005, 07:52 AM
dug dug is offline
Registered User
 
Join Date: Apr 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old April 7th, 2005, 09:07 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
DatePart Calculations ninel SQL Language 2 February 29th, 2008 03:08 AM
DateTime Calculations mrookey C# 4 June 2nd, 2006 06:18 AM
UTC DateTime to Local DateTime r_ganesh76 SQL Server 2000 1 April 4th, 2005 08:21 AM
DateTime calculations sudarshan73 SQL Server 2000 1 March 15th, 2005 03:13 PM
calculations between records plenarts Access 5 December 30th, 2004 08:05 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.