MySQL: converting date formats inside of strings

Question

I have a list of file paths and dates stored in a database:

               path          |   date
_____________________________|___________
C:\folder\file1 %Y-%m-%d.csv | 2016-09-14
C:\folder\file2_%M %d %Y.csv | 2016-09-13
C:\folder\file3 %y%m%d.csv   | 2016-08-31

The dates in the file paths are according to the STR_TO_DATE format convention. The dates will change everyday. I need to write a SELECT query that will return:

               result 
_________________________________
C:\folder\file1 2016-09-14.csv 
C:\folder\file2_Sep 14 2016.csv 
C:\folder\file3 160831.csv

I don't want to end up writing a never-ending REPLACE query with all the possible scenarios:

REPLACE(... REPLACE(REPLACE(path,'%Y',YEAR(date)),'%d',DAY(date))...)

Is there a way to do this with a MySQL built-in function?

gen_Eric · Accepted Answer · 2016-09-15 15:29:26Z

2

You want DATE_FORMAT() here. It should replace format strings it recognizes with their values and ignore everything else.

SELECT DATE_FORMAT(date, path) AS result;

_{NOTE: %M Will give the full month ("September"), for the abbreviated month ("Sept") use %b.}

DEMO: http://sqlfiddle.com/#!9/77b6f7/1

answered Sep 15, 2016 at 15:29

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL: converting date formats inside of strings

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related