How to Convert Julian Date to Gregorian in MS SQL

Julian date is stored in YYYDDD format, for example 12/31/99 will be stored as 99365 and 01/01/2017 will be stored as 117001.

If you need to convert the date from YYYDDD to any conventional date format like dd/mm/yyyy in SQL use steps below.

The idea is to convert year and date portions separately and them add them together and use CONVERT function to get any date format that you require.

Here is a script that shows the conversion process step-by-step.

SELECT
-- Limit output for 9 rows
TOP 9
-- 00 Julian date - 91244
 FAEFTB,
-- 01 Greg year - 1991
 cast(left(CAST(FAEFTB as decimal)+1900000, 4) as char(4)) AS GREGYEAR,
-- 02 Julian Days - 243
 cast(right(CAST(FAEFTB as decimal)+1900000, 3) as int)-1 AS JULDAYS,
-- 03 Greg Days - 1900-09-01
 dateadd(day,0, cast(right(CAST(FAEFTB as decimal)+1900000, 3) as int)-1) AS GREGDAYS,
-- 04 Add Greg Year and Greg Days - 1991-09-01
 cast(left(CAST(FAEFTB as decimal)+1900000, 4) as char(4)) + dateadd(day,0,
 cast(right(CAST(FAEFTB as decimal)+1900000, 3) as int)-1) AS YY_DAYS,
-- 05 Complete Convesion Syntax in yyyy/mm/dd format (111)
CASE FAEFTB
WHEN 0 THEN '' ELSE
CONVERT(VARCHAR(10),
 cast(left(CAST(FAEFTB as decimal)+1900000, 4) as char(4))
 + dateadd(day,0, cast(right(CAST(FAEFTB as decimal)+1900000, 3) as int)-1),
111)
END AS FAEFTB111
FROM JDE_CRP.CRPDTA.F1201
WHERE
-- Dates are between 01/01/1920 and 04/10/1971
FAEFTB BETWEEN 20000 AND 71100

Resulting output format is yyyy/mm/dd (format code 111).  If you need any other output format, pick the appropriate format code from this documentation page – “CAST and CONVERT (Transact-SQL)”

https://msdn.microsoft.com/en-us/library/ms187928.aspx

Here is a sample output for this script:

This example demonstrates that conversion work properly for both leap and non-leap years. See that 53335 and 64336 are both properly converted to December 1 of corresponding year.

If script above is not enough, here is further explanation.

Step 00 – Take a raw Julian date from database: 71091.

Step 01 – 1900000 to it and grab left 4 characters: 1971.

Step 02 – From 1971091 take right 3 characters; convert to INT and subtract 1:    91 – 1 = 90.

Step 03 – Add 90 days to zero date (1/1/1900) to get  to 4/1/1900

Step 04 – Concatenate year 1971 with date 4/1/1900 to arrive at 4/1/1971.

Step 05 – Complete conversion logic includes a check for *Zero date.  In that case no conversion is needed and we will get *Blank.