T-SQL Function to Convert Julian Date to Standard Date

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

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

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

About onlinejt

Blogger

No comments yet.

Leave a Reply

*