Search code examples
sqlsql-serverjulian-date

Convert Julian Date to YYYY-MM-DD

I have searched far and wide, but I can't seem find a way to convert julian to yyyy-mm-dd.

Here is the format of my julian:

The Julian format consists of the year, the first two digits, and the day within the year, the last three digits.

For example, 95076 is March 17, 1995. The 95 indicates the year and the 076 indicates it is the 76th day of the year.

15260

I have tried this but it isn't working:

dateadd(d,(convert(int,LAST_CHANGED_DATE) % 1000)-1, convert(date,(convert(varchar,convert(int,LAST_CHANGED_DATE) /1000 + 1900) + '/1/1'))) as GrgDate
Solution

  • You can select each part of the date using datepart()

    SELECT DATEPART(yy, 95076), DATEPART(dy, 95076)

    +++EDIT: I misunderstood something. Here's my correction: +++++

    SELECT DATEADD(day, CAST(RIGHT('95076',3) AS int) – 1, CONVERT(datetime,LEFT('95076',2) + '0101', 112))