Time conversion
-
I am trying to convert the run_duration (int column in sysjobhistory) to time giving details about how much time a job took to complete. Is there an easy way to do this. any help will be greatly apprecaited.
TIA
-
Since the column is in the format of HHMMSS (even though it's a number),
For just a pure string representation of the time...
STUFF(STUFF(run_duration,5,0,':'),3,0,':')
For a real time as a datetime datatype...
CONVERT(DATETIME,STUFF(STUFF(run_duration,5,0,':'),3,0,':'))
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff
I don't know whether this is just due to a setting on our server, but the run_duration values don't have a fixed length. Some are 11638, while others are just 9. That means that you will not be able to STUFF at fixed lengths, because the length sometimes is only 1.
I tried your suggestions on our server, and the returned values were NULL (probably because STUFF won't work on INT data). That means that one will first have to convert the INT into character data before attempting the STUFFs.
Regards
Schalk
-
I do this
STUFF(STUFF(RIGHT('000000' + CAST(run_duration AS varchar(6)),6),5,0,':'),3,0,':')
Far away is close at hand in the images of elsewhere.
Anon. -
Then try
STUFF(STUFF(Right('00000' + Cast(run_duration as varchar(6)),6),5,0,':'),3,0,':')
-
Schalk and Antares,
I test this stuff before I post it and, you are incorrect. STUFF works just fine on an INT values thanks to implicit conversions. For example, this is the test I did and it works without doing the conversion you suggest (unless you've somehow overridden implicit conversions) ...
DECLARE @Run_Time INT
SET @Run_Time = 123456
SELECT STUFF(STUFF(@Run_Time,5,0,':'),3,0,':')
and here's the result
-------------------
12:34:56
(1 row(s) affected)
That, not-with-standing, Antares is correct about the 5 digit times when the time is less than 10 AM. This next bit of code should do it for you.
STUFF(STUFF(REPLACE(STR(Run_Time,6),' ','0'),5,0,':'),3,0,':')
In case you have more doubt, here's the code I tested it with...
DECLARE @Run_Time INT
SET @Run_Time = 12345
SELECT STUFF(STUFF(REPLACE(STR(@Run_Time,6),' ','0'),5,0,':'),3,0,':')
...Which returns...
-------------------
01:23:45
(1 row(s) affected)
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
p.s.
If you are getting a "NULL' as a return, perhaps the code you wrote isn't actually finding what you think. Post your code and we'll take a look.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Try this
DECLARE @Run_Time INT
SET @Run_Time = 9
SELECT STUFF(STUFF(@Run_Time,5,0,':'),3,0,':')
The value is INT type yes, but if it ran for 9 seconds it is 9 not 000009. You will understand the reason I suggets the convert then.
-
Antares,
I apologize if the time lag between our posts caused this...
I had reposted a solution as follows...
STUFF(STUFF(REPLACE(STR(Run_Time,6),' ','0'),5,0,':'),3,0,':')
The original question was how to convert the Run_Time column of the SysJobHistory table. According to BOL, the column data type is INT and the format is hhmmss. STR(x,6) converts any INT to a six character string with leading blanks. The REPLACE converts those blanks to 0's. In other words, the suggestion I posted does the same thing as your CAST/Concatenate/RIGHT.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yep I may just not have caught the change.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply