is there mySQL function to convert a date from format dd.mm.yy to YYYY-MM-DD?
for example, 03.09.13 -> 2013-09-03.
Asked
Active
Viewed 7.5k times
13
-
2Is your input a date type, or is it a string that's literally `03.09.13`? – Ed Gibbs Sep 03 '13 at 16:28
-
@ppeterka66 Yes, and asking here probably means that I didn't find/understood an answer for that. – user2216190 Sep 03 '13 at 16:28
-
@EdGibbs it's a string. – user2216190 Sep 03 '13 at 16:29
-
Convert your string to a date and then back to a string in the new format. – Dan Bracuk Sep 03 '13 at 16:30
-
@user2216190 : simple answer is no. I would imagine if you installed some UDF library for regex, you could do something way over the top with regex matching and concocting a new date string. You'd be better off using your dynamic language to pre-process the date. Are you using PHP? – Lee Sep 03 '13 at 16:30
-
@DanBracuk it needs to automatically as possible - we are transfering a part of a database to a new format... – user2216190 Sep 03 '13 at 16:31
-
@Lee yes, PHP. but if it is too complicated to do it is possible to just leave it. What's your solution? – user2216190 Sep 03 '13 at 16:33
-
you could use the substr example posted below. It's not what i'd recommend or even do myself, but its the only thing that will work as an in-query thing – Lee Sep 03 '13 at 16:35
-
If you're transferring to a new format, I'd strongly recommend changing the column to a `DATE` type. It will work much better like that, and you'll never have to worry about invalid values. – Ed Gibbs Sep 03 '13 at 16:36
-
@EdGibbs that's what we are doing – user2216190 Sep 03 '13 at 16:37
-
wouldn't you want `2013-09-03` when given `03.09.13` instead of `2013-09-13` – amaster Sep 03 '13 at 16:39
-
Looks like Ed Gibbs come to the rescue, didnt realise that function ever existed. – Lee Sep 03 '13 at 16:40
-
If your new datatype is going to be date, why are you worried about the format at all? – Dan Bracuk Sep 03 '13 at 16:43
-
Becouse we are changing the whole databases - and some data needs to be transfered as possible. – user2216190 Sep 03 '13 at 16:46
-
@EdGibbs That worked, Greate solution! – user2216190 Sep 03 '13 at 16:49
3 Answers
45
Since your input is a string in the form 03.09.13, I'll assume (since today is September 3, 2013) that it's dd.mm.yy. You can convert it to a date using STR_TO_DATE:
STR_TO_DATE(myVal, '%d.%m.%y')
Then you can format it back to a string using DATE_FORMAT:
DATE_FORMAT(STR_TO_DATE(myVal, '%d.%m.%y'), '%Y-%m-%d')
Note that the year is %y (lowercase "y") in STR_TO_DATE and %Y (uppercase "Y") in DATE_FORMAT. The lowercase version is for two-digit years and the uppercase is for four-digit years.
Ed Gibbs
- 25,924
- 4
- 46
- 69
-
4DATE_FORMAT(STR_TO_DATE(myVal, '%d.%m.%Y'), '%Y-%m-%d') Y is capital letter – R D Dec 08 '14 at 02:38
-
1Rajavel - it's a lowercase `y` in the first one because the input string has a 2-digit year (`03.09.13`). In the second one it's uppercase so the output will be a 4-digit year (`2013-09-03`). – Ed Gibbs Dec 08 '14 at 12:16
4
Use
SELECT CONCAT(
'20',
SUBSTR('03.09.13', 7, 2),
'-',
SUBSTR('03.09.13', 4, 2),
'-',
SUBSTR('03.09.13', 1, 2))
Fiddle demo.
More about formats you can read in the corresponding manual page.
Tip: if this is about conversion value from non-datetime field - better to use DATE/DATETIME data type instead. However, this is a bad idea to operate with dates via string functions. Above there is a nice trick with STR_TO_DATE (will not repeat that code, updated to fit better)
Alma Do
- 37,009
- 9
- 76
- 105
-
Won't work, `DATE_FORMAT` requires the input date to be in Y-m-d format anyway. Your query (if you run it) will return `2003-09-13`, obviously incorrect – Lee Sep 03 '13 at 16:27
-
Reverted my vote to an upvote, purely because i didn't think of this way. Not something i'd personally recommend (as im sure you wouldnt either), but given his unique requirements, seems to be the only way of doing it. – Lee Sep 03 '13 at 16:33
-
@Lee - yes, I disagree with such method of getting result, yet in tha same time it will fit for OP. I really can't understand why people using string date types for dates. – Alma Do Sep 03 '13 at 16:35
-
-
My requirement was to convert a string dd-mm-yy to dd-mm-yyyy. I modified the above code as below `SELECT CONCAT( SUBSTR('03.09.13', 1, 2), '-', SUBSTR('03.09.13', 4, 2), '-', '20', SUBSTR('03.09.13', 7, 2))` – Ananda Apr 24 '16 at 14:25
1
Dates are stored using an internal format. You can use the function date_format() to convert it to a string using a variety of formats. For yours in particular:
select date_format(`date`, '%Y-%m-%d')
Gordon Linoff
- 1,242,037
- 58
- 646
- 786