SQL function to convert Julian Date

Dear Reader,

Earlier I have already written a logic to convery Julian to Datetime in C#

today we had a requirment to convert the same in SQL server. So wrote a function for this opetaion

Syntax:

Create FUNCTION dbo.from_julian(@julian char(6)) RETURNS datetime AS

BEGIN

RETURN (select DATEADD(YEAR, @julian / 1000,DATEADD(DAY, @julian % 1000,'18991231')))

END

//TEST Query

--select dbo.from_julian(110269)

Explaination of conversion Logic:

(select DATEADD(YEAR, @julian / 1000,DATEADD(DAY, @julian % 1000,'18991231')))

18991231 is the last day in 19^th century

Step #1:

Select (DATEADD(DAY, @julian % 1000,'18991231'))

Exmaple: Select (DATEADD(DAY, 110269 % 1000,'18991231'))

Output: 1900-09-26 00:00:00.000

Step#2:

(select DATEADD(YEAR, @julian / 1000, Step#1))

(select DATEADD(YEAR, @julian / 1000, 1900-09-26 00:00:00.000))

(select DATEADD(YEAR, 110269 / 1000, 1900-09-26 00:00:00.000))

Select (110269 / 1000)

Output: 110

Adding Year 110 + 1900 = 2010

Result is datetime: 2010-09-26 00:00:00.000

Hope It Helps

4Z ~ Fauzi