SQL Server 2014 – Date Time Format for msdb.dbo.agent_datetime Function

sql serversql server 2014

Looking at msdb.dbo.agent_datetime scalar value function. What date / time format is this expressed in, UTC? Same question for msdb.dbo.sysjobs columns : run_date, run_time

Best Answer

Are you asking about the format or the value?

By "value" I mean if it is in UTC or the local datetime value.

The function doesn't have a stored value, it work with what you pass into it and returns a datetime value based on that. I.e., there's no UTC relevance for this.

The columns, for instance in sysjobhistory, are not in UTC. They are in the local timezone (for your SQL Server instance).

As for the format:

The tables uses a pair of int columns - one for date and one for time. So the int 20191223 means 2019-12-23 (I'm using ISO 8609 format when communicating datetime values).

The function accepts two parameters, both int. One for the date and one for the time, as described above. It returns a datetime value (which has no format - it is just a value).

In case you wonder how I know it returns a datetime and not datetime2(3): You can investigate a value by casting it to sql_variant and then investigate properties of that value using the SQL_VARIANT_PROPERTY function:

DECLARE @a sql_variant
SET @a = dbo.agent_datetime(20201023,154500)
SELECT SQL_VARIANT_PROPERTY(@a, 'BaseType')

Above returns:

datetime
Related Question