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)