Convert Julian Date to YYYY-MM-DD

sql sql-server julian-date
Mochael_DLite picture Mochael_DLite · Nov 5, 2015 · Viewed 32.2k times · Source

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

Answer

MikeVe picture MikeVe · Nov 5, 2015

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))

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
OR is not supported with CASE Statement in SQL Server

The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this? CASE ebv.db_no WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500' ELSE 'WECS 9520' END as wecs_system

Read More