MSSQL, 줄리안데이트 <-> DATETIME 바꾸는 함수

DATETIME -> 줄리안데이트

CREATE   function [dbo].[DATETIME_TO_JULIAN](@date as datetime) returns int
as
begin
 
-- DataTime 값을 줄리안데이트 형식으로 변환
 
    declare @return        as int,
            @tempdate      as char(8),
            @basedate      as datetime,
            @tempyyyy      as int,
            @interval      as int
    
    set @tempdate = convert(char(8), @date, 112)
 
    set @tempyyyy = year(@date) -- cast(left(@tempdate, 4) as int)
    set @basedate = convert(datetime, left(@tempdate, 4) + '0101', 112)
    set @interval = datediff(d, @basedate, @date) + 1
 
    set @return = (@tempyyyy - 1900) * 1000 + @interval
 
    return @return
 
end

실행하기

SELECT DBO.DATETIME_TO_JULIAN('20120101')

 

줄리안데이트 -> DATETIME

CREATE FUNCTION [dbo].[JULIAN_TO_DATETIME](@date as int) RETURNS DATETIME
AS
BEGIN
-- 줄리안데이트를 DateTime으로 변환
declare @return        as datetime,        -- Return Value
		@tempyyyy      as int,                    -- Year
		@interval      as int

-- Year, Month, Day 계산
set @tempyyyy = 1900 + convert(int, @date / 1000)
set @interval = convert(int, right(convert(varchar(6), @date), 3)) - 1
set @return = dateadd(d,@interval,convert(datetime,convert(char(4),@tempyyyy)+'0101',112))
return @return
END

실행하기

SELECT DBO.JULIAN_TO_DATETIME(112001)