I am querying an API and I got a date like this: "2019-04-17T14:04:24.224-04:00". I suppose this is an iso date. How can I store the year, month and day on a mysql database? I want the following format "dd/mm/yyyy".
date("d-m-Y", strtotime("2019-04-17T14:04:24.224-04:00"));
Just do this,
Try this!
update TABLENAME
set date_columnname = LEFT(date_present_columnname,10)
Formats are a display concern, that is it's a function of who is looking at the data, so you don't do it until you know who's looking at it. At the point of display you apply the format required by the user's locale, and quite often adjust for their local time-zone as well.
MySQL's DATE column type is expressed in ISO-8601 format like you have, so you should be able to insert that minus everything after the "T".
So either strip that out before insertion and it should be fine.
On display you can use any of the PHP date formatting functions to get the exact format you want. This is often locale specific.
You'll want to be sure that the time expressed is in the correct time-zone. Do any conversion necessary to get it in the right zone before converting to a date or you may find you're getting the wrong date for several hours of the day.
Generally you should:
DATE or DATETIME fields and convert to/from localized formats as dictated by user preferences.