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
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
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)
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?)
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)
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)
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
DECLARE @date INT, @time INT
SELECT @date=20150119, @time=171911
FROM dbo.int_to_datetime (@date, @time)