SQL SERVER – Interesting Function AGENT_DATETIME

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)

,
Previous Post
SQL SERVER – The Basics of the File System Task, Part 1 – Notes from the Field #071
Next Post
SQL SERVER – SCRIPT – SQL Listening on Dynamic Port?

Related Posts

11 Comments. Leave new

  • Sanjay Monpara
    March 13, 2015 1:14 pm

    Its really useful, no need to concatenate two string and datatype conversion !

    Reply
  • Don’t be modest Pinal Dave, nothing about SQL surprises you.

    Reply
  • Leonardo Torres
    March 18, 2015 2:09 am

    another way: SELECT CAST(‘20150119 17:19:11’ AS DATETIME)

    Reply
  • I would also consider the permission constraints of allowing access to the msdb. This may not be best practice.

    Reply
  • 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?

    Reply
  • swapnil dane
    May 18, 2016 7:35 pm

    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.

    Reply
  • 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” ??

    Reply
  • Russell Harrell
    September 7, 2018 7:59 pm

    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.

    Reply
    • 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.

      Reply

Leave a Reply

Menu