June 7, 2012 at 10:05 am
Hi friends
I have start time and end time , I need to get is the time difference between 2datetimes
something like below
Idstart_time End_time time_diff
16496 2012-05-19 06:01:36.767 2012-05-19 06:01:36.783 (calculate hrs,minutes,secs,milli secs )
June 7, 2012 at 10:19 am
You can just use simple math to achieve this.
create table #Date(Id int, start_time datetime, End_time datetime)--, time_diff
insert #Date
select 16496, '2012-05-19 06:01:36.767', '2012-05-19 06:01:36.783' union all
select 18496, '2012-05-19 06:01:36.767', '2012-05-21 06:01:36.783' union all
select 1975, '2012-05-19 06:01:36.767', GETDATE()
select *, End_time - start_time from #Date
drop table #Date
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 7, 2012 at 10:31 am
I'm thinking this is more what is needed:
USE [Sandbox] -- My playground, change as needed
GO
/****** Object: UserDefinedFunction [dbo].[itvfn_FormatTimeHHMMSS] Script Date: 06/07/2012 10:08:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.itvfn_FormatTimeHHMMSSMS(
@TimeInMilliSeconds INT
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
(SELECT
CASE WHEN @TimeInMilliSeconds < 0 THEN '-' ELSE '' END +
CAST(ABS(@TimeInMilliSeconds)/3600000 AS VARCHAR) + ':' +
RIGHT('0' + CAST((ABS(@TimeInMilliSeconds)%3600000)/60000 AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(((ABS(@TimeInMilliSeconds)%3600000)%60000)/1000 AS VARCHAR), 2) + ':' +
RIGHT('00' + CAST(((ABS(@TimeInMilliSeconds)%3600000)%60000)%1000 AS VARCHAR), 3) AS FormatTime)
;
GO
create table #Date(Id int, start_time datetime, End_time datetime)--, time_diff
insert #Date
select 16496, '2012-05-19 06:01:36.767', '2012-05-19 06:01:36.783' union all
select 18496, '2012-05-19 06:01:36.767', '2012-05-21 06:01:36.783' union all
select 1975, '2012-05-19 06:01:36.767', GETDATE()
select Id, start_time, End_time, FormatTime, End_time - start_time
from #Date CROSS APPLY dbo.itvfn_FormatTimeHHMMSSMS(DATEDIFF(ms,start_time,End_time));
drop table #Date
June 7, 2012 at 7:24 pm
Or, if I'm not mistaken, this should also work:
DECLARE @Date TABLE (Id int, start_time datetime, End_time datetime)--, time_diff
INSERT @Date
SELECT 16496, '2012-05-19 06:01:36.767', '2012-05-19 06:01:36.783' UNION ALL
SELECT 18496, '2012-05-19 06:01:36.767', '2012-05-21 06:01:36.783' UNION ALL
SELECT 1975, '2012-05-19 06:01:36.767', GETDATE() UNION ALL
SELECT 1982, '2011-01-01 15:30:20.234', '2011-04-05 13:20:42.111'
SELECT [ddd:hh:mi:ss:mmm]=CAST(MyDays AS VARCHAR) + ':' + CONVERT(VARCHAR(16), MyDate, 114)
FROM (
SELECT End_time - start_time, CAST(End_time - start_time AS INT)
FROM @Date) x(MyDate, MyDays)
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply