Jeff Moden (1/8/2012)
To add to the reasons not to use undocumented or, sometimes, even documented MS provided code...the developers of SQL Server will occasionally make significant performance mistakes in their code just like the rest of us.
That's extremely generous of you Jeff. My impression is that the SQL code behind SSMS and in some of the system databases like msdb, must be written by a completely different team: the SQL code is most often pretty shonky, if we're honest. This isn't a particularly bad example, but it could/should have been written as an in-line function:
CREATE FUNCTION dbo.agent_datetime_inline
(
@Date integer,
@Time integer
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
SELECT
CONVERT(datetime,
CONVERT(nvarchar(4), @Date/ 10000) + N'-' +
CONVERT(nvarchar(2),(@Date % 10000)/100) + N'-' +
CONVERT(nvarchar(2), @Date % 100) + N' ' +
CONVERT(nvarchar(2), @Time / 10000) + N':' +
CONVERT(nvarchar(2),(@Time % 10000)/100) + N':' +
CONVERT(nvarchar(2), @Time % 100),
120) AS date_time
GO
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi