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
The result is
The result is
Reference: Pinal Dave (https://blog.sqlauthority.com)
Its really useful, no need to concatenate two string and datatype conversion !
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 am getting the error when value is empty. If I pass any value as NULL, my output comes as NULL. May be you should pass 19000101 and 0 for date and time respectively for empty/null/zero values.
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.