One in a while I come across such interesting side of SQL Server that it even surprises me. Just the other day I came across function AGENT_DATETIME. I had never used it before and now I just found usage example of the same.
There can be several methods to convert integer values to a datetime value. But did you know that there is a system function in MSDB database that does this job? It is AGENT_DATETIME function. It accepts two parameters one is date value in YYYYMMDD format and another is time value in HHMMSS format
SELECT MSDB.DBO.AGENT_DATETIME(20150119,0)
The result is
2015-01-19 00:00:00.000
SELECT MSDB.DBO.AGENT_DATETIME(20150119,171911)
The result is
2015-01-19 17:19:11.000
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
15 Comments. Leave new
Its really useful, no need to concatenate two string and datatype conversion !
Thanks @Sanjay
Don’t be modest Pinal Dave, nothing about SQL surprises you.
Oh no. sometimes it does :)
another way: SELECT CAST(‘20150119 17:19:11’ AS DATETIME)
I would also consider the permission constraints of allowing access to the msdb. This may not be best practice.
Hi, i tried using this function, its really useful.. though it doesn’t accept 0 value for the date parameter. i’m trying to display all job schedule(will still display even if its 0). any workarounds in mind?
AGENT_DATETIME function is not documented in MSDN. Does it mean it may not be available in upcoming versions of the SQL Server?
I know it is most unlikely. But, if it gets removed in upcoming versions, we can’t blame Microsoft and will have to hunt down and modify scripts where we have used it.
How to use MSDB.DBO.AGENT_DATETIME If you want result lool like “2015-01-19 17:19:11” or “2015-01-19 17:19” ??
I am receiving a conversion error since the date and time fields are NULL. What approach would you recommend as a workaround?
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
I wonder whether there’s a “reverse” version of this function?
i.e. convert datetime values to these int values?
thank you a lot Pinal.
Very helpful! Thank you for this suggestion, much appreciated.
I use AGENT_DATETIME to retrieve run datetime from [msdb].[dbo].[sysjobhistory]. eg [msdb].[dbo].[AGENT_DATETIME]([run_date],[run_time]) which returns 20240910 , 82030 as : 2024-09-10 08:20:30.000
Thanks, I have found this post very useful and I use it to avoid using the system function. :)