Julian Date Conversion SQL

Question

How to convert the Julian date to Gregorian Calendar date in SQL Server? Below are the cases:

1. 6 digit number - I have done the conversion and it is working fine

2. 5 digit number - I don't have any idea about how to convert this Julian date to normal date. I did search in Google websites, but each websites have different answers, so I am confused. Example data (47315, 45009, 69064, 99281, 98222, 98329)

3. If I have the value "0" in the Julian date column, then how to convert this value to Gregorian date?

Answer 1

That second scenario looks like YYJJJ format.

Converting it would look something like

SELECT DATEADD(dd, CAST(RIGHT(yourdate, 3) AS integer) - 1, CONVERT(smalldatetime,'01/01/' + LEFT(yourdate, 2), 3)) AS converteddate
FROM <yourtable>