{"id":17112,"date":"2022-02-11T09:40:05","date_gmt":"2022-02-11T08:40:05","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/"},"modified":"2022-05-31T16:34:36","modified_gmt":"2022-05-31T14:34:36","slug":"sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/","title":{"rendered":"SQL Server: Last duration for all SQL Server jobs in TIME format from HHmmss to HH:mm:ss"},"content":{"rendered":"<p>For a customer, I want to see all last duration for a job and have a good format for it.<br \/>\nThe query to do it is very simple (at the beginning\u2026) but when I try to have a better view it was more complicated.<\/p>\n<p>Let me try to explain you.<\/p>\n<p>As you now, to have the history of all jobs on SQL Server, we use system views <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-tables\/dbo-sysjobhistory-transact-sql?view=sql-server-ver15\" target=\"_blank\" rel=\"noopener\">dbo.sysjobhistory<\/a> and <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-tables\/dbo-sysjobs-transact-sql?view=sql-server-ver15\" target=\"_blank\" rel=\"noopener\">dbo.sysjobs<\/a><\/p>\n<p>I use this query to have the last date and run duration:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">USE msdb\nGO\nSELECT DISTINCT j.Name AS job_name,jh.run_date AS last_run_date, jh.run_duration as run_duration\nFROM sysjobhistory jh, sysjobs j\nWHERE jh.job_id = j.job_id AND j.Name like 'DM_%'  AND run_status = 1\nAND jh.run_date = \n(SELECT MAX(jhm.run_date) FROM sysjobhistory jhm WHERE jh.job_id = jhm.job_id)\nORDER BY jh.run_duration desc\nGO<\/pre>\n<p>A little example with the query and maintenance jobs:<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory01.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-54353 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory01.png\" alt=\"\" width=\"300\" height=\"143\" \/><\/a><\/p>\n<p>The run time is an INT as data type where the format is HHmmss.<br \/>\nThis mean for the job DBIntegrityCheck &#8211; USER_DBs in my example, we have 5749 representing 57 minutes and 49 seconds.<\/p>\n<p>I thinking to convert it in a time format to have a better view and have HH:mm:ss and not HHmmss.<\/p>\n<p>I try with CAST and CONVERT and to convert also first to varchar:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SELECT CAST (5749 as TIME)\nGO\nSELECT CAST ( CAST(5749 as VARCHAR) as TIME)\nGO\nSELECT CONVERT (TIME, 5749)\nGO\nSELECT CONVERT (TIME,CONVERT (VARCHAR, 5749))\nGO\n<\/pre>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory02.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-54354 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory02.png\" alt=\"\" width=\"300\" height=\"243\" \/><\/a><\/p>\n<p>The simple CAST and CONVERT to TIME does not working with the error:<\/p>\n<p style=\"color: red\">Explicit conversion from data type int to time is not allowed.<\/p>\n<p>The CAST and CONVERT to TIME with first a conversion to varchar works but the result is not good:<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory03.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-54355 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory03.png\" alt=\"\" width=\"300\" height=\"272\" \/><\/a><\/p>\n<p><strong>What can I do?<\/strong><\/p>\n<p>I will use msdb.dbo.agent_datetime(YYYYmmdd, HHmmss) to convert it because it\u2019s exactly the format I need for the time. I use 19000101 as default date and do a cost for the TIME:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SELECT MSDB.DBO.AGENT_DATETIME(19000101,5749)\nSELECT cast (MSDB.DBO.AGENT_DATETIME(19000101,5749) as time)\n<\/pre>\n<p>Running the query\u2026<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory04.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-54356 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory04.png\" alt=\"\" width=\"300\" height=\"153\" \/><\/a><\/p>\n<p>It\u2019s really the result that I search to have.<\/p>\n<p>Now I put this conversion through msdb.dbo.agent_datetime in my query:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">USE msdb\nGO\nSELECT DISTINCT j.Name AS job_name,jh.run_date AS last_run_date, jh.run_duration as run_duration,\ncast (MSDB.DBO.AGENT_DATETIME(jh.run_duration,5749) as time) as run_duration_time\nFROM sysjobhistory jh, sysjobs j\nWHERE jh.job_id = j.job_id AND run_status = 1\nAND jh.run_date = \n(SELECT MAX(jhm.run_date) FROM sysjobhistory jhm WHERE jh.job_id = jhm.job_id)\nORDER BY jh.run_duration desc\nGO\n<\/pre>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory04b.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-54357 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory04b.png\" alt=\"\" width=\"300\" height=\"124\" \/><\/a><\/p>\n<p>After running the query, I receive an error message:<\/p>\n<p style=\"color: red\">Msg 241, Level 16, State 1, Line 3<br \/>\nConversion failed when converting date and\/or time from character string.<\/p>\n<p><strong>Why this error\u2026<\/strong><br \/>\nAfter regarding the result on the first screenshot, the bigger time is 311342 and it\u2019s more than 24hours!<br \/>\nThe format TIME is limited from 00:00:00.0000000 through 23:59:59.9999999 folowing the documentation<a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-tables\/dbo-sysjobhistory-transact-sql?view=sql-server-ver15\" target=\"_blank\" rel=\"noopener\"> here\u00a0<\/a><\/p>\n<p>If we run the query separately:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SELECT MSDB.DBO.AGENT_DATETIME(19000101,311342)\nSELECT cast (MSDB.DBO.AGENT_DATETIME(19000101,311342) as time)\n<\/pre>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory05.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-54358 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory05.png\" alt=\"\" width=\"300\" height=\"94\" \/><\/a><\/p>\n<p>If I run the query with 23:59:59, it\u2019s working:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SELECT MSDB.DBO.AGENT_DATETIME(19000101,235959)\nSELECT cast (MSDB.DBO.AGENT_DATETIME(19000101,235959) as time)\n<\/pre>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory06.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-54359 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory06.png\" alt=\"\" width=\"300\" height=\"140\" \/><\/a><\/p>\n<p>If I run the query with 24:00:0, it\u2019s not working:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SELECT MSDB.DBO.AGENT_DATETIME(19000101,240000)\nSELECT cast (MSDB.DBO.AGENT_DATETIME(19000101,240000) as time)\n<\/pre>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory07.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-54360 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory07.png\" alt=\"\" width=\"300\" height=\"95\" \/><\/a><\/p>\n<p>Again it\u2019s not possible to convert upper 240000\u2026<br \/>\nUsing msdb.dbo.agent_datetime is definitively not the good idea for what I need.<br \/>\nThe next step is to convert each number in time unit through varchar and to format it.<\/p>\n<p>I take this time my worth case and convert into time unit:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">select (311342 \/ 10000) % 100 as hour,\n       (311342 \/ 100) % 100 as minute,\n       (311342 \/ 10) % 100 as second\n<\/pre>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory08.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-54361 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory08.png\" alt=\"\" width=\"300\" height=\"157\" \/><\/a><\/p>\n<p>As workaround, I try to transform it to Time:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">select dateadd(hour, (311342 \/ 10000) % 100,\n       dateadd(minute, (311342 \/ 100) % 100,\n       dateadd(second, (311342) % 100,\n       dateadd(millisecond, 0, cast('00:00:00' as time)))))\n<\/pre>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory09.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-54362 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory09.png\" alt=\"\" width=\"300\" height=\"154\" \/><\/a><\/p>\n<p>And the result is 07:13:42 and not like expected 31:13:42\u2026<\/p>\n<p>Finally to have a solution in the Time format that I will, I need to use the format varchar(8):<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">select Cast ((311342 \/ 10000) % 100 as varchar) + ':' +\n       Cast ((311342 \/ 100) % 100 as varchar) + ':' +\n       Cast ((311342) % 100 as varchar)\n<\/pre>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory10.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-54363 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory10.png\" alt=\"\" width=\"300\" height=\"127\" \/><\/a><\/p>\n<p>I insert it in the query to have the duration for each job:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">USE msdb\nGO\nSELECT DISTINCT j.Name AS job_name,jh.run_date AS last_run_date, jh.run_duration as run_duration,\nCast ((jh.run_duration \/ 10000) % 100 as varchar) + ':' +\n       Cast ((jh.run_duration \/ 100) % 100 as varchar) + ':' +\n       Cast ((jh.run_duration) % 100 as varchar) as run_duration_time\nFROM sysjobhistory jh, sysjobs j\nWHERE jh.job_id = j.job_id AND run_status = 1 \nAND jh.run_date = \n(SELECT MAX(jhm.run_date) FROM sysjobhistory jhm WHERE jh.job_id = jhm.job_id)\nORDER BY jh.run_duration desc\nGO\n<\/pre>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory11.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-54364 aligncenter\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory11.png\" alt=\"\" width=\"300\" height=\"157\" \/><\/a><\/p>\n<p>Now as you can see, the format of the duration is like I wish at the beginning with a lot test and improvement\u2026<br \/>\nI hope this script and steps can help you to understand conversion to TIME data type.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>For a customer, I want to see all last duration for a job and have a good format for it. The query to do it is very simple (at the beginning\u2026) but when I try to have a better view it was more complicated. Let me try to explain you. As you now, to have [&hellip;]<\/p>\n","protected":false},"author":15,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[49,51],"type_dbi":[],"class_list":["post-17112","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-microsoft","tag-sql-server"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>SQL Server: Last duration for all SQL Server jobs in TIME format from HHmmss to HH:mm:ss - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server: Last duration for all SQL Server jobs in TIME format from HHmmss to HH:mm:ss\" \/>\n<meta property=\"og:description\" content=\"For a customer, I want to see all last duration for a job and have a good format for it. The query to do it is very simple (at the beginning\u2026) but when I try to have a better view it was more complicated. Let me try to explain you. As you now, to have [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-02-11T08:40:05+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-05-31T14:34:36+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory01.png\" \/>\n<meta name=\"author\" content=\"St\u00e9phane Haby\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"St\u00e9phane Haby\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/\"},\"author\":{\"name\":\"St\u00e9phane Haby\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b\"},\"headline\":\"SQL Server: Last duration for all SQL Server jobs in TIME format from HHmmss to HH:mm:ss\",\"datePublished\":\"2022-02-11T08:40:05+00:00\",\"dateModified\":\"2022-05-31T14:34:36+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/\"},\"wordCount\":507,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory01.png\",\"keywords\":[\"Microsoft\",\"SQL Server\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/\",\"name\":\"SQL Server: Last duration for all SQL Server jobs in TIME format from HHmmss to HH:mm:ss - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory01.png\",\"datePublished\":\"2022-02-11T08:40:05+00:00\",\"dateModified\":\"2022-05-31T14:34:36+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory01.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory01.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server: Last duration for all SQL Server jobs in TIME format from HHmmss to HH:mm:ss\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b\",\"name\":\"St\u00e9phane Haby\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"caption\":\"St\u00e9phane Haby\"},\"description\":\"St\u00e9phane Haby has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. St\u00e9phane Haby is Microsoft Most Valuable Professional (MVP) as well as Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals &amp; Pharmaceuticals, Banking \/ Financial Services, and many other industries.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/stephane-haby\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Server: Last duration for all SQL Server jobs in TIME format from HHmmss to HH:mm:ss - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server: Last duration for all SQL Server jobs in TIME format from HHmmss to HH:mm:ss","og_description":"For a customer, I want to see all last duration for a job and have a good format for it. The query to do it is very simple (at the beginning\u2026) but when I try to have a better view it was more complicated. Let me try to explain you. As you now, to have [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/","og_site_name":"dbi Blog","article_published_time":"2022-02-11T08:40:05+00:00","article_modified_time":"2022-05-31T14:34:36+00:00","og_image":[{"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory01.png","type":"","width":"","height":""}],"author":"St\u00e9phane Haby","twitter_card":"summary_large_image","twitter_misc":{"Written by":"St\u00e9phane Haby","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/"},"author":{"name":"St\u00e9phane Haby","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b"},"headline":"SQL Server: Last duration for all SQL Server jobs in TIME format from HHmmss to HH:mm:ss","datePublished":"2022-02-11T08:40:05+00:00","dateModified":"2022-05-31T14:34:36+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/"},"wordCount":507,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory01.png","keywords":["Microsoft","SQL Server"],"articleSection":["Database Administration &amp; Monitoring","Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/","name":"SQL Server: Last duration for all SQL Server jobs in TIME format from HHmmss to HH:mm:ss - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory01.png","datePublished":"2022-02-11T08:40:05+00:00","dateModified":"2022-05-31T14:34:36+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory01.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/02\/jobhistory01.png"},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-last-duration-for-all-sql-server-jobs-in-time-format-from-hhmmss-to-hhmmss\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server: Last duration for all SQL Server jobs in TIME format from HHmmss to HH:mm:ss"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b","name":"St\u00e9phane Haby","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","caption":"St\u00e9phane Haby"},"description":"St\u00e9phane Haby has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. St\u00e9phane Haby is Microsoft Most Valuable Professional (MVP) as well as Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals &amp; Pharmaceuticals, Banking \/ Financial Services, and many other industries.","url":"https:\/\/www.dbi-services.com\/blog\/author\/stephane-haby\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17112","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/15"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=17112"}],"version-history":[{"count":1,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17112\/revisions"}],"predecessor-version":[{"id":17113,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17112\/revisions\/17113"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=17112"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=17112"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=17112"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=17112"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}