Julian day is used in the Julian date (JD) system of time measurement for scientific use by the astronomy community. Julian date is recommended for astronomical use by the International Astronomical Union.
I have this particular system where the data was stored in the database as Julian Date format. Here is an example of that data:
Julian Date Data
This is how the function can be used in a script:
SELECT JULIANDATE,
dbo.ConvertJulianToGregorianDate(JULIANDATE) as ConvertedDate
from dbo.DWTESTTABLE
Here are the results:
Converted Date
Here is the T-SQL function:
CREATE FUNCTION dbo.ConvertJulianToGregorianDate
(
@JulianDate INT
) RETURNS DATETIME
AS
BEGIN
DECLARE @DATEVALUE AS DATETIME
SELECT
@DATEVALUE = CASE
WHEN @JulianDate = 0 OR @JulianDate < 639906
THEN convert(datetime, '1753-01-01')
ELSE dateadd(day,@JulianDate-729960,convert(datetime, '07-25-99'))
END
RETURN @DATEVALUE
END