asked
t-sql for time difference between two datetime sql server 2008 R2
Need to find time difference between two dates in hh:mm:ss format. Should be able to take I tried 2 options. Option 1 gives correct answer in most cases but hard to use the logic in a t-sql where I have to include other columns in a complex query. Is it possible to use something like option 2? Thanks for help! --option 1 fails if given following dates --set @startdate = '2004-10-19 07:53:35.000' --set @enddate = '2004-10-18 11:59:57.000' option 1: declare @startdate datetime declare @enddate datetime set @enddate = '2004-10-18 07:53:35.000' set @startdate = '2004-10-18 15:28:57.000' declare @finaltime nvarchar(9) if @startdate>@enddate begin declare @temp datetime set @temp = @startdate set @startdate = @enddate set @enddate = @temp set @finaltime = '-' end if @finaltime is not null begin set @finaltime = @finaltime + CONVERT(nvarchar(2),(datediff(ss,@startdate,@enddate)/3600)) + ':' + CONVERT(nvarchar(2),((datediff(ss,@startdate,@enddate)%3600)/60)) + ':' + CONVERT(nvarchar(2),(datediff(ss,@startdate,@enddate)%60)) + ':' end else set @finaltime = CONVERT(nvarchar(2),(datediff(ss,@startdate,@enddate)/3600)) + ':' + CONVERT(nvarchar(2),((datediff(ss,@startdate,@enddate)%3600)/60)) + ':' + CONVERT(nvarchar(2),(datediff(ss,@startdate,@enddate)%60)) select @finaltime as TimeDifference --gives correct value = -7:35:22 ----------------------------------------------------------- option 2: declare @startdate datetime declare @enddate datetime set @enddate = '2004-10-18 07:53:35.000' set @startdate = '2004-10-18 15:28:57.000' declare @finaltime nvarchar(9) select convert(time(0),(@enddate - @startdate)) as TimeDifference --gives me incorrect value 16:24:38
Is that a good idea? Intervals are prehaps more usefully modelled using a number of time granules, preferably an integer at the smallest applicable granule e.g. `SELECT DATEDIFF(MINUTE, '2004-10-18T11:59:57.000', '2004-10-19T07:53:35.000');` result `1194` (minutes). Formatting can then be done on the 'front end'.
It looks like this should do it: if @startdate >= @enddate select convert(varchar(8), @startdate - @enddate, 8); else select convert(varchar(8), @enddate - @startdate , 8); Please note that any type of conversion in T-SQL is evil because it is a job of the front end code to do whatever formatting conversions are needed, but if you have to use T-SQL then you might consider the snippet above. Oleg
@SBhagat Negative number? In which format do you want to display it? The script above calculates the values correctly in hh:mm:ss format provided that the difference between the dates is within 24 hours. If the difference between the dates is more than that than the hh:mm:ss format does not make any sense anyway, so it is probably not the case. What I mean is that if the difference between 2 dates is, say, 5 days 3 hours and 5 minutes then you surely would not want to have a the difference displayed as 123:05:00 because it would make it look silly and also will no longer comply with your initial hh:mm:ss format. In a mean time if you need to add the minus sign to the result when the @startdate < @enddate then just do it: if @startdate >= @enddate select convert(varchar(8), @startdate - @enddate, 8); else select '-' + convert(varchar(8), @enddate - @startdate , 8); What I mean it the script calculates the difference correctly and if you do need to add the minus sign somewhere then add it :)
