May 13, 2015 at 1:25 pm
I found this nifty code on stackoverflow that works well but I'm trying to send the results to a text file and the column lengths are huge. I used CAST for the first line and it worked great but I can't seem to make it work with duration. Here's the original code:
SELECT
j.name,
h.run_status,
durationHHMMSS = STUFF(STUFF(REPLACE(STR(h.run_duration,7,0),
' ','0'),4,0,':'),7,0,':'),
[start_date] = CONVERT(DATETIME, RTRIM(run_date) + ' '
+ STUFF(STUFF(REPLACE(STR(RTRIM(h.run_time),6,0),
' ','0'),3,0,':'),6,0,':'))
FROM
msdb.dbo.sysjobs AS j
INNER JOIN
(
SELECT job_id, instance_id = MAX(instance_id)
FROM msdb.dbo.sysjobhistory
GROUP BY job_id
) AS l
ON j.job_id = l.job_id
INNER JOIN
msdb.dbo.sysjobhistory AS h
ON h.job_id = l.job_id
AND h.instance_id = l.instance_id
ORDER BY
CONVERT(INT, h.run_duration) DESC,
[start_date] DESC;
So as shown below, I was able to CAST jobname but I need to do it to DURATION and nothing seems to work.
SELECT
CAST(j.name as Varchar(50)) as JOBNAME,
h.run_status AS ENABLED,
DURATION = STUFF(STUFF(REPLACE(STR(h.run_duration,7,0),
' ','0'),4,0,':'),7,0,':'),
[start_date] = CONVERT(DATETIME, RTRIM(run_date) + ' '
+ STUFF(STUFF(REPLACE(STR(RTRIM(h.run_time),6,0),
' ','0'),3,0,':'),6,0,':'))
FROM
Can someone show me how to run a CAST on DURATION?
May 13, 2015 at 1:35 pm
Any particular datatype you want? To CAST a value you have to wrap the value in the cast function.
_______________________________________________________________
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/
May 13, 2015 at 1:37 pm
Hello and welcome to the forums.
What data type do you want to cast it to? You can wrap your column with a CAST function, but why? What are you looking to do with the column?
SELECT
CAST(j.name as Varchar(50)) as JOBNAME,
h.run_status AS ENABLED,
DURATION = CAST(STUFF(STUFF(REPLACE(STR(h.run_duration,7,0),
' ','0'),4,0,':'),7,0,':') AS Varchar(30)),
[start_date] = CONVERT(DATETIME, RTRIM(run_date) + ' '
+ STUFF(STUFF(REPLACE(STR(RTRIM(h.run_time),6,0),
' ','0'),3,0,':'),6,0,':'))
FROM
May 13, 2015 at 1:39 pm
If you mean cast it to a time / datetime, you can't safely do that, because the run_duration in sysjobhistory can meet or exceed 24 hours.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 13, 2015 at 2:12 pm
Hi all. Thanks for the replies. VARCHAR is fine. The whole reason I need to cast is because when I output the results to notepad, it wordwraps line on line and makes the output look terrible. When I specify a value, such as in the first line (using 50), it shortens that column way up and makes everything look perfect in my notepad report that I email to myself via blat.
Mr Wagner, above, took care of it for me. I thought I had tried that already (was putting cast everywhere I could think of!) but apparently not because I just tried it and voila.. look great now. Thanks so much for the help!
May 14, 2015 at 5:12 am
g3463420 (5/13/2015)
Hi all. Thanks for the replies. VARCHAR is fine. The whole reason I need to cast is because when I output the results to notepad, it wordwraps line on line and makes the output look terrible. When I specify a value, such as in the first line (using 50), it shortens that column way up and makes everything look perfect in my notepad report that I email to myself via blat.Mr Wagner, above, took care of it for me. I thought I had tried that already (was putting cast everywhere I could think of!) but apparently not because I just tried it and voila.. look great now. Thanks so much for the help!
Thanks for the feedback. Honestly, I didn't think that was what you were after because you had already done a CAST on the job name, so I figured you wanted something else. I'm glad it got you what you were after.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply