-1

My dilema,

I was requested to populate a table of sample data and I completed the command to add a date.

I entered '07-07-2016' as sample data. When running a query the "date_ordered" field came back as "0000-00-00".

I need to convert the already stored data to the MM-DD-YYYY format. I would prefer to convert the data completely, not just the view.

Thanks

Improve this question
6
  • Please tag the RDBMS you are using and version'
    – TheGameiswar
    Jul 18, 2016 at 14:58
  • Are you storing dates as varchar(string/text) type or date type... If 1., you should not store date as string (whichever RDBMS is used).
    – Julien Vavasseur
    Jul 18, 2016 at 15:00
  • I have the data set to 'date'. Which is why the return data is completely different than what I entered. If I would have stored it as a string, it would have came back as how I entered it.
    – baldchicken
    Jul 18, 2016 at 15:14
  • What's your question?
    – mustaccio
    Jul 18, 2016 at 15:55
  • 1
    You wrote: "Which is why the return data is completely different than what I entered. If I would have stored it as a string, it would have came back as how I entered it". Storing DATEs as VARCHARs is a very bad idea. There is a bit of fiddling with formatting but soon becomes second nature. The main thing is that it confuses the optimizer and could slow your system. Plus, datetime functions won't work with strings.
    – Vérace - Слава Україні
    Jul 18, 2016 at 19:37

1 Answer 1

Reset to default
6

If your column date_ordered is of type DATE, MySQL will store it in its own way. To display it in the format you want, you should use the function date_format():

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

e.g. For DD-MM-YYYY format, you want to use the format string '%d-%m-%Y'

select date_format (date_ordered,'%d-%m-%Y') from your_table;

If you want to store the date in that format, use a VARCHAR instead of a DATE type, but then you will have to typecast the string to a date before performing any other date-based functions on it. Better to let MySQL store the date how it wants, and convert it to the desired format when displaying.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.