2

How to Create a colummn DATE with format DD/MM/YYYY? Mysql come for defaut with this formar YYY/MM/DD.

I need isert via HTML5 date Day/Month/Year, for this format come in DD/MM/YYYY

Pty
  • 27
  • 2
  • 1
    Why not let MySql store the date how it likes to then when selecting records via a query, format the date then. – Linger Dec 06 '13 at 17:58
  • It might help you http://stackoverflow.com/questions/8338031/mysql-setup-the-format-of-datetime-to-dd-mm-yyyy-hhmmss-when-creating-a-tab – M Khalid Junaid Dec 06 '13 at 18:00

3 Answers3

2

On your insert/select statements, you can convert MySQL Date Functions:

SELECT ... DATE_FORMAT(field,'%d/%m/%Y');

INSERT ... STR_TO_DATE('formatteddate','%d/%m/%Y')
jeffjenx
  • 17,041
  • 6
  • 57
  • 99
  • Are you suggesting storing a date field as a string (varchar) instead? – Linger Dec 06 '13 at 18:07
  • No, I'm suggesting converting to the proper MySQL DATE format so you don't lose additional DATE functionality. – jeffjenx Dec 06 '13 at 18:10
  • Use `DATE_FORMAT` for selecting data and converting it to your preferred format. Use `STR_TO_DATE` for converting dates in your preferred format to the MySQL standard. – jeffjenx Dec 06 '13 at 18:12
  • can you show a small example, how: ALTER TABLE `users` ADD `date` DATE NOT NULL – Pty Dec 06 '13 at 18:22
  • 1
    *@Pty*, here is the [**SQL Fiddle**](http://sqlfiddle.com/#!2/5f3de/3/1) example of what [**MrSlayer Answer**](http://stackoverflow.com/a/20430590/1253219) is doing. As you can see his answer is just converting the date from the format you have to the format MySql stores dates as. Then with his SELECT statement his is pulling the date from the data base and displaying again how you want it. He is simply showing you how to perform what I had originally commented on your question. His answer is the best way of handling it. – Linger Dec 06 '13 at 18:29
0

you can check your js file if any,corresponding to date and change format to MySQL database format

user3067299
  • 25
  • 4
0

I guess you could save it as a varchar, but that's messy. Your best option is to have MySQL save the date in the default format, then simply format the date to DD/MM/YYYY upon retrieval.

Ace Hyzer
  • 345
  • 3
  • 10
  • Sure, so you store it as YYYY/MM/DD, but when you access that data with whatever program you are writing to access it, you can convert that date upon retrieval of the record. This will work unless the only place you are using the date is within the raw database itself. – Ace Hyzer Dec 06 '13 at 18:24