So I have military date formats in my MySql database and I need to change them to regular mysql dates. Some of these dates are like this:

8001

For January 1st, 2008. Some of them are like this:

10001

For January 1st, 2010. First, the day of year inverse conversion for mysql is %b. Thus to change day 001 to 1 jan for the string 08001 you would use:

select distinct str_to_date(‘08001’, “%y%j”) from mytable

which returns:

1/1/2008 12:00:00 AM

Which is what we want. Now to deal with the different string sizes, just add a ‘0’ to everything, then take the right 5 digits from the resulting string to get your differing string size problem taken care of:

substr(concat(‘0’,jdate), length(concat(‘0’,jdate))-4,length(concat(‘0’,jdate)))

Putting it all together to get a date out of this mess:

select distinct str_to_date(substr(concat(‘0’,jdate), length(concat(‘0’,jdate))-4,length(concat(‘0’,jdate))), “%y%j”) from mytable