[函數]日期時間轉換[int2datatime]

最近在查詢sysjobhistory資料表的時候，發現其中run_date日期與run_time時間兩欄位的的資料型別都是int，要拿來跟datatime資料型別比較日期先後順序有點麻煩，上網找了找，果然在sqldev.net看到已經有高人寫好函數了，這樣我就不用自己寫啦，我改了一下內容再貼過來囉


create function [dbo].[fn_intDateTime2DateTime] (@intDate int, @intTime int)
returns datetime
as
begin
--宣告變數
declare @year int, @month int, @day int,
@hour int, @min int, @sec int
Declare @rtnDate datetime ,@strDate nvarchar(40)
--日期處理
set @year = (@intDate / 10000)
set @month = (@intDate - (@year * 10000)) / 100
set @day = (@intDate - (@year * 10000) - (@month * 100))
--時間處裡
set @hour = (@intTime / 10000)
set @min = (@intTime - (@hour * 10000)) / 100
set @sec = (@intTime - (@hour * 10000) - (@min * 100))
--格式轉換
select @strDate = convert(nvarchar(4), @year) + N'-' + convert(nvarchar(2), @month) + N'-' + convert(nvarchar(4), @day) + N' ' +
replace(convert(nvarchar(2), @hour) + N':' + convert(nvarchar(2), @min) + N':' + convert(nvarchar(2), @sec), ' ', '0')

select @rtnDate = convert(datetime, @strDate)

return @rtnDate

end



go



SELECT sysjobhistory.run_date AS 'int日期', sysjobhistory.run_time AS 'int時間' ,dbo.fn_intDateTime2DateTime(sysjobhistory.run_date,sysjobhistory.run_time)AS 'datetime時間'FROM sysjobhistory