We’ve got data that uses a funky date format — not a true julian date but the Year followed by the DAY of the year.
i.e.
2010005 = January 5th, 2010
2010031 = January 31st 2010
2010365 = December 31st 2010
This little snippet of code will allow you to select it from the mysql database and convert it to a real date.
select date_sub(date_add(concat(substr(jdate,1,4),’-01-01′), interval substr(jdate,5,3) day ), interval 1 day)
so in this example below returns May 2 ( the 123rd day of 2004 is May 2 )
select date_sub(date_add(concat(substr(‘2004123′,1,4),’-01-01′), interval substr(‘2004123’,5,3) day ), interval 1 day)