Skip to content

Dirceu Resende

DBA SQL Server and BI Analyst (PowerBI, SSAS, SSIS, SSRS)

  • Consultancy
    • BI Consulting
    • Power BI Consulting
    • SQL Server Consulting
  • File
  • Series
    • Certification
    • Security and Audit
    • Performance tuning
    • What has changed in T-SQL?
    • Data Protection
  • Jobs
  • Data Platform Events
  • About
  • Contact

Other Languages

Subscribe to a blog by email

Enter your email address to subscribe to this blog and receive notifications of new publications by email.

Join 536 other subscribers

Blog Views

1.645.456 views

Categories

  • Apache / .htaccess (9)
  • Database (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Audit (15)
      • Azure (2)
      • CLR (53)
      • Query Development (83)
      • DMVs and Catalog Views (31)
      • Errors (22)
      • Tools (12)
      • Data Formatting and Validation (23)
      • Little Known Features (19)
      • Hacks (17)
      • Easy (30)
      • File Handling (13)
      • Maintenance (80)
      • Monitoring (35)
      • What not to do (7)
      • OLE Automation (19)
      • Performance tuning (22)
      • Python (1)
      • Safety (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Career and Courses (13)
  • Career, Courses and Certifications (28)
  • Cell Phones (1)
  • Events and Lectures (63)
  • Programme (57)
    • C # (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Uncategorized (10)
  • SEO (4)
  • Virtualization (5)

Microsoft MVP Data Platform

My Certifications

Training

Posts Archive

Recent Posts

  • Black Friday discounts on SQL Server Trainings (Buy my kkkkk course) November 27th, 2020
  • SQL Server - The “new” GREATEST and LEAST functions November 27th, 2020
  • SQL Server - How to know the date of a user's last login November 9th, 2020
  • Azure in Free Practice # 07 - Administering Databases in Azure November 5th, 2020
  • Analysis Services - An error occurred while opening the model on the workspace database. Reason: An unexpected error occurred (file 'tmcachemanager.cpp', function 'TMCacheManager :: CreateEmptyCollectionsForAllParents') November 5th, 2020
  • 7 May 2015
  • 1
  • Database DMVs and Catalog Views Data Formatting and Validation SQL Server SQL Server Agent

How to convert the run_date and run_time columns from job_history to datetime in SQL Server

Views: 399
Reading Time: 2 minutes

Hello guys,
Good day.

Today I will briefly show you how to convert the run_date and run_time columns from the msdb.dbo.sysjobhistory database catalog table to datetime. Currently, the run_date column is a varchar in the format yyyymmdd (Ex: 07 / 05 / 2015 = 20150507), and the run_time column is a time in the format hmmss (Ex: 08: 27: 00 = 82700). You can even understand visually what these values ​​mean, but the calculation with these dates and times is much more complicated.

SQL Server - Job History for Datetime - 1

To make it easier to query this information, we can combine these 2 columns and convert them to datetime. I will demonstrate two ways on how to do this:

Using the msdb.dbo.agent_datetime system function

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
    j.name,
    h.step_id,
    h.step_name,
    h.run_status,
    h.message,
    [RunDateTime] = msdb.dbo.agent_datetime(h.run_date, h.run_time),
    h.run_date,
    h.run_time
FROM
    [msdb].[dbo].[sysjobs] j
    JOIN [msdb].[dbo].sysjobhistory h ON j.job_id = h.job_id
WHERE
    h.run_status = 0 AND h.step_id = 0

Creating a custom function

Although there is already a system function to do this (Thanks to Caroline goltara, for the tip), many DBA's do not like to grant access to system functions for analysts, even though they know what function it does. To get around this, we can create a custom role and give analysts access to that role.

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
USE [Util]
GO
CREATE FUNCTION [dbo].[fncJobs_Converte_Datetime] (
    @DATE INT,
    @TIME INT
)
RETURNS datetime
AS BEGIN
    DECLARE @Date_Time datetime
    DECLARE @Ds_Date VARCHAR(8) = @DATE
    DECLARE @Ds_Time VARCHAR(8) = @TIME
    IF (@DATE = 0) RETURN NULL
    SET @Ds_Time = RIGHT('000000'+@Ds_Time,6)
    SET @Ds_Time = SUBSTRING(@Ds_Time,1,2)+':'+SUBSTRING(@Ds_Time,3,2)+':'+SUBSTRING(@Ds_Time,5,2)
    SET @Date_Time = CAST(@Ds_Date + ' ' + @Ds_Time AS datetime)
    RETURN @Date_Time    
END

And now, let's use the function created to display the results:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
    j.name,
    h.step_id,
    h.step_name,
    h.run_status,
    h.message,
    [RunDateTime] = Util.dbo.fncJobs_Converte_Datetime(h.run_date, h.run_time)
FROM
    [msdb].[dbo].[sysjobs] j
    JOIN [msdb].[dbo].sysjobhistory h ON j.job_id = h.job_id
WHERE
    h.run_status = 0
    AND h.step_id = 0

The end result is exactly the same using both functions, as in the image below:
SQL Server - Job History for Datetime - 2

tags: agent_datetimeconvertconverterdatedatadatetimehistoryjobjob_historymsdbrun_daterun_timesqlsql server

You may also like ...

  • SQL Server - How to create a hidden friend draw using Transact-SQL

  • SQL Server - User only connects to the instance with sysadmin permission - Login failed for user 'teste'. Reason: Login-based server access validation failed with an infrastructure error

  • SQL Server - Importing and Exporting data from Excel spreadsheets

  • Next Timeout when running Queries via Linked Server on SQL Server
  • Previous How to estimate how much time is left to finish the backup on SQL Server?

1 Response

  • Comments1
  • Pingbacks0
  1. Maruan Afif said:
    24 November 2015 to 16: 33

    Oops… Ball show
    I was looking for exactly that ..
    system function saved ..
    abras!

    Reply

Leave a Comment Cancel reply

Dirceu Resende © 2020. All Rights Reserved.