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.

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)