MySQL : Converting Strings AM to PM

Question

i have a column of Strings like 2015/10/14 07:00:00 i need to convert them to DateTime, my problem is :

07:00 is in pm time

i need to get that in a format of 19:00

my code:

UPDATE `DATE_tests`
      SET `newDate` = STR_TO_DATE(`oldDate`, '%Y/%m/%d %r')

to be precise i need 2015/10/14 07:00:00 to be 2015-10-14 19:00:00.

i can get so far 2015-10-14 07:00:00

Use `%H` instead of `%r`. The formats are very well documented: https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_date-format. — Gordon Linoff, Oct 31 '15 at 13:46
@gordon-linoff i did Use %H instead of %r and still same result — Mounir Elfassi, Oct 31 '15 at 13:48
How do you know it's 19:00 and not 7:00 if there's no AM/PM? — dnoeth, Oct 31 '15 at 14:34
@dnoeth i scraped the data from a website and all the data are pm's — Mounir Elfassi, Oct 31 '15 at 15:03

score 2 · Accepted Answer · edited Oct 31 '15 at 14:17

2

You should fix the table and store dates as dates, not as strings. But you need to add 12 hours to do what you want. Something like this

UPDATE `DATE_tests`
      SET `newDate` = DATE_FORMAT(DATE_ADD(STR_TO_DATE(`oldDate`, '%Y/%m/%d %r'), interval 12 hour), '%Y-%m-%d %H:%i:%s');

edited Oct 31 '15 at 14:17

Suchit kumar

answered Oct 31 '15 at 13:55

Gordon Linoff

i got this error `SQL syntax; ... near 'hours), '%Y-%m-%d %H:%i:%s')'` – Mounir Elfassi Oct 31 '15 at 14:02
DATE_FORMAT(DATE_ADD(STR_TO_DATE('2015/10/14 07:00:00', '%Y/%m/%d %r'), interval 12 HOUR), '%Y-%m-%d %H:%i:%s') @MounirElfassi – Abhishek Sharma Oct 31 '15 at 14:15
ok i found the error in`interval 12 hours)` it should be `interval 12 hour)` whit no s. Thank you – Mounir Elfassi Oct 31 '15 at 14:15

score 1 · Answer 2 · edited May 23 '17 at 11:44

1

try:INTERVAL 12 HOUR

UPDATE `DATE_tests`
      SET `newDate` = DATE_FORMAT(DATE_ADD(STR_TO_DATE(`oldDate`, '%Y/%m/%d %r'), INTERVAL 12 HOUR), '%Y-%m-%d %H:%i:%s');

edited May 23 '17 at 11:44

Community

answered Oct 31 '15 at 14:15

Suchit kumar

2 Answers2