SQL Tips: Exploring Agent_Datetime Function

Introduction

Recently there was a discussion in one of the forums which helped me to learn about a function which has been there for a long time  in SQLServer and had eluded my knowledge till now.
This article explains on the details of the function along with the common use cases.

Agent_Datetime 

The function of interest is agent_datetime.
It resides in the msdb database of a SQLServer instance and accepts two arguments. First argument would be an eight digit integer which represents date portion of a datetime value. The second argument would be a six digit integer representing the time part of the datetime value in hours,minutes and seconds format.

The function can be invoked as below

Select msdb.dbo.agent_datetime(20171027,231134)

The result would be as below

As you see it created a datetime value 2017-10-27 23:11:34.000 from the two integer values passed.

Background

There is a clear intention behind naming the function as Agent_Datetime and placing it within the msdb database.
The function is not officially documented and is basically used internally for converting the date and time values related to sql jobs from msdb system tables.

Use Cases

1. Get SQL Agent related datetime values from integer based columns in msdb catalog objects

There are few columns like for ex. rundate and runtime in sysjobhistory table which represented a date value stored as integer internally.
So query like below



SELECT j.name,dbo.agent_datetime(jh.run_date,jh.run_time) AS RunDate
FROM dbo.sysjobs j
JOIN dbo.sysjobhistory jh
ON jh.job_id = j.job_id

Will give you the list of jobs with their run dates in proper datetime format from msdb database.

2. Generic use to generate datetime values from integer based date and time fields

Though the function is created in msdb we're free to use it in our user databases to merge integer based date and time values to a proper datetime value.

As an illustration see the below

The above screenshot shows three different illustrations regarding use of agent_datetime function with different types of arguments.

The first example passes 8 digit date and 6 digit time based integer values and merges them into a proper datetime value.

The second examples shows what happens when you pass a 4 digit integer value for the time part instead of the expected 6 digits. The function will interpret it in the format mm:ss and returns merged date with the minute and seconds value. If you have only hour and minute parts make sure you pass it in hhmm00 format (i.e adding 0s for the missing seconds)

The final illustration depicts the case where you've only a date part to pass. In this case, you need to pass a default value of '' for the time part when you invoke the agent_datetime function. The function will return full datetime value with time part being default (00:00:00)

Conclusion

As seen from the illustrations above the msdb.dbo.agent_datetime scalar function can be used to generate proper datetime values from two separate integer based date and time values. It can be used for sql agent based catalog queries in msdb as well as for the queries in other databases. 

The only caveat is that since its undocumented its better to refrain from using this on production code especially for the cases where we want the query to be run periodically. 

For adhoc querying we shall use the function as a quick way to generate datetime values from integer date and time equivalents.