|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: 2 days ago @ 6:28 PM
Points: 180,
Visits: 513
|
|
Hi All, I have 58 sequentially run jobs as part of our Database ETL. So, I took the start time of the 1st job and EndTime of 58th Job to calculate the totla Run time of the ETL.
I'm using the following TSQL logic to get the time difference between the jobs but if the ETL is > than 24 hours, I'm not getting the correct difference in hours. Please suggest.
Need to calculate the difference between 2012-12-16 20:57:01.293 and 2012-12-18 01:26:57.573
When I use: DATEDIFF(HH,Start.WorkQueueStartWorkDate,Finish.WorkQueueEndWorkDate) RunTimeInHours I get 20 Hrs
When I use convert(varchar,(Finish.WorkQueueEndWorkDate - Start.WorkQueueStartWorkDate),108) RunTime I get 04:29:56 Please help me getting the correct difference as Runtime.
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: 2 days ago @ 6:28 PM
Points: 180,
Visits: 513
|
|
Never mind I figure out a way to get the right Datetime...
convert(varchar(5),DateDiff(s, Start.WorkQueueStartWorkDate,Finish.WorkQueueEndWorkDate)/3600)+':'+convert(varchar(5),DateDiff(s, Start.WorkQueueStartWorkDate,Finish.WorkQueueEndWorkDate)%3600/60)+':'+convert(varchar(5),(DateDiff(s, Start.WorkQueueStartWorkDate,Finish.WorkQueueEndWorkDate)%60)) as [hh:mm:ss]
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 32,923,
Visits: 26,811
|
|
Mac1986 (12/22/2012) I'm using the following TSQL logic to get the time difference between the jobs but if the ETL is > than 24 hours...
... then I'd have to say that you might have a bit of a performance problem.
If it were me, I'd add another timer to measure the duration of each of the 58 jobs and the fix the job that's taking the longest and maybe continue from there.
--Jeff Moden "RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:56 PM
Points: 1,324,
Visits: 1,778
|
|
SELECT CAST(DATEDIFF(SECOND, Start.WorkQueueStartWorkDate, Finish.WorkQueueEndWorkDate) / 3600 AS varchar(3)) + RIGHT(CONVERT(char(8), Finish.WorkQueueEndWorkDate - Start.WorkQueueStartWorkDate, 8), 6) from ( select CAST('20121216 20:57:01.293' AS datetime) AS WorkQueueStartWorkDate ) AS start cross join ( select CAST('20121218 01:26:57.573' AS datetime) AS WorkQueueEndWorkDate ) as finish
SQL DBA,SQL Server MVP('07, '08, '09) One man with courage makes a majority. Andrew Jackson
|
|
|
|