Lynn Pettis (5/2/2014)
Instead of all the conversions you could use the scalar function msdb.dbo.agent_datetime.For example:
declare @rundate int, @runtime int;
set @rundate = 20140502;
set @runtime = 512;
select msdb.dbo.agent_datetime(@rundate,@runtime);
While that's convenient, if you ever need performance out of it, I don't believe you'll get it. Here's the code from that function. It's hard for me to believe they do conversions to NCHAR in this numeric-only process...
CREATE FUNCTION agent_datetime(@date int, @time int)
RETURNS DATETIME
AS
BEGIN
RETURN
(
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)
)
END
--Jeff Moden
Change is inevitable... Change for the better is not.