SQL SERVER – Alternate to AGENT_DATETIME Function

Sometimes back I posted about interesting function AGENT_DATETIME functions which will convert integers into datetime values. Someone commented that there might be some permission issues using this function which is only available in msdb database. So I write this post to give you alternate method without using any such functions.

SQL SERVER - Alternate to AGENT_DATETIME Function agent_function

Create two variables that have values for date part and time part

DECLARE @date INT, @time INT
SELECT @date=20150119, @time=171911
SELECT MSDB.DBO.AGENT_DATETIME(@date,@time)

The result is

2015-01-19 17:19:11.000

Here is the same result not using MSDB.DBO.AGENT_DATETIME function

DECLARE @date INT, @time INT
SELECT @date=20150119, @time=171911
SELECT CAST(CAST(@date AS CHAR(8))+' '+STUFF(STUFF(@time,3,0,':'),6,0,':') AS DATETIME)

The result is

2015-01-19 17:19:11.000

The idea is that you know the date and time part. All you need is to concatenate them and use the CAST function to make it as valid datetime. The time part is integer so using the STUFF function you can add colon to make it in hh:mm:ss format and concatenate with the date part suffixing a space and this formatted time and finally use the CAST function to make it as DATETIME value.

Let me know what think of this function and its replacement. Have you ever used this function in your production system? If yes, please leave a comment as I would love to hear about it.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL DateTime, SQL Function, SQL Scripts, SQL Server
Previous Post
SQL SERVER – How to Install SSMS From Command Line? Error: The Specified Value for Setting ‘MEDIALAYOUT’ is Invalid.
Next Post
SQL SERVER – How to Update Two Tables in One Statement?

Related Posts

4 Comments. Leave new

  • I do use the AGENT_DATETIME function in a server health check query. I like to check on failed jobs, and hated trying to read the integer date / times from sysjobhistory.

    While it would be nice if Microsoft would update SQL to store this information in a datetime, I can understand why they’ve not made such a change (how many 3rd party and custom scripts likely exist that would need to be re-written?)

    Reply
  • Nice function but it fails when the hour part of the time is < 10 because there are only 5 digits and STUFF puts the colons in the wrong place. My solution (there could be many) is to REVERSE the digits, STUFF the colons, then REVERSE the result for the CAST.
    SELECT CAST(CAST(@rundate AS CHAR(8)) + ' ' + REVERSE(STUFF(STUFF(REVERSE(@runtime), 3, 0, ':'), 6, 0, ':')) AS DATETIME)

    Reply
  • Eric, your function is still incomplete, it fails when the job runs at midnight for example.
    This one works for all, worked for me.
    select cast(cast(@d as char(8))+’ ‘+stuff(stuff(right(‘000000’+convert(varchar(6),@t),6),3,0,’:’),6,0,’:’) as datetime)

    Reply
  • Thomas Franz
    May 28, 2019 3:46 pm

    Why are you not using the functions, that specialised for a job? Converting dateparts into a datetime is clearly the job for DATETIMEFROMPARTS() (of course it will fail, if you specify an invalid date as 20190230):

    CREATE FUNCTION dbo.int_to_datetime (@date Int, @time int)
    RETURNS TABLE AS return
    SELECT DATETIMEFROMPARTS(@date / 10000, @date / 100 % 100, @date % 100, @time / 10000, @time / 100 % 100, @time % 100, 0) AS run_datetime
    ;
    GO
    DECLARE @date INT, @time INT
    SELECT @date=20150119, @time=171911
    SELECT *
    FROM dbo.int_to_datetime (@date, @time)

    Reply

Leave a Reply