Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Producing dates from SYSJOBS and SYSJOBHISTORY Expand / Collapse
Author
Message
Posted Tuesday, January 13, 2009 4:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 26, 2012 7:28 AM
Points: 3, Visits: 7
I admit I wrote some pretty bad code and forgot to replace "history." with "jh.", but that's been fixed. During the testing process I did a little edge testing and the code worked just fine. I plugged your example of "115" into my code and it correctly added 1 minute, 15 seconds to the start time. I'm not sure why you received different results.


Post #635923
Posted Sunday, June 13, 2010 4:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 3:24 AM
Points: 159, Visits: 131
Or you could just forget doing all the formatting yourself and use the msdb sys function dbo.agent_datetime(@date int no default,@time int no default)

so to get the run date and time

SELECT dbo.agent_datetime(run_date,run_time)

or to get the completed date time

SELECT DATEADD(s,DATEDIFF(s,dbo.agent_datetime(run_date,0),dbo.agent_datetime(run_date,run_duration)),dbo.agent_datetime(run_date,run_time))

Post #936504
Posted Sunday, March 25, 2012 7:38 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 14, 2013 12:05 PM
Points: 47, Visits: 79
thanks, probably the best working code dealing with the subject. I replaced the datetime with time(0), just easier on my eyes:

CAST(STUFF(STUFF(REPLACE(STR(run_time, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS time(0)) AS runtime,
CAST(STUFF(STUFF(REPLACE(STR(run_duration, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS time(0)) AS duration
Post #1272345
Posted Monday, March 26, 2012 7:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 26, 2012 7:28 AM
Points: 3, Visits: 7
BitWise MnM (3/25/2012)
thanks, probably the best working code dealing with the subject. I replaced the datetime with time(0), just easier on my eyes:

CAST(STUFF(STUFF(REPLACE(STR(run_time, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS time(0)) AS runtime,
CAST(STUFF(STUFF(REPLACE(STR(run_duration, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS time(0)) AS duration

It's probably worth mentioning that TIME(0) was added to T-SQL in SQL Server 2008.



Post #1272648
Posted Monday, November 19, 2012 2:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 3:05 AM
Points: 2, Visits: 83

Thanks a lot Timothy ....really helped me a lot ... Really appreciated it

Regards,
Bharath
Post #1386249
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse