Skip to content

Instantly share code, notes, and snippets.

@gebelo

gebelo/gist:4aee93ba6d622b42ced6953881310b14

Created June 7, 2016 18:28
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save gebelo/4aee93ba6d622b42ced6953881310b14 to your computer and use it in GitHub Desktop.
Save gebelo/4aee93ba6d622b42ced6953881310b14 to your computer and use it in GitHub Desktop.
Download ZIP
Ugly but effective MySQL conversion of m/d/y string into yyyy-mm-dd date
Raw
gistfile1.txt
SELECT dob, SUBSTRING_INDEX( `dob` , '/', 1 ) AS mo,
SUBSTRING_INDEX(SUBSTRING_INDEX( `dob` , '/', 2 ),'/',-1) AS dy,
SUBSTRING_INDEX(SUBSTRING_INDEX( `dob` , '/', 3 ),'/',-1) AS yr,
CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX( `dob` , '/', 3 ),'/',-1),"-",SUBSTRING_INDEX( `dob` , '/', 1 ),"-",SUBSTRING_INDEX(SUBSTRING_INDEX( `dob` , '/', 2 ),'/',-1)) AS dt
FROM table
"dob" "mo" "dy" "yr" "dt"
"7/19/1970" "7" "19" "1970" "1970-7-19"
#the dt field becomes 1970-07-19 when stored in a date field via update query
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment