12

When I make a table and create a field of type date in mysql, it stores date like 0000-00-00. Is it possible to change the format to 'd-m-Y'?

swegi
  • 4,046
  • 1
  • 26
  • 45
SohailRajput
  • 629
  • 1
  • 7
  • 18

5 Answers5

14

Go to MySQL Reference - 10.5. Data Type Storage Requirements

Search for: Storage Requirements for Date and Time Types

Dates are internally stored as A three-byte integer packed as DD + MM×32 + YYYY×16×32

But, if you select a date column for display, it has to be shown in some way, so it comes out as 0000-00-00. It is not stored as a char(10) with that specific format.

If, for display, you need to see a specific format, you can convert it to VARCHAR in a specific format using Date_Format(). However, bear in mind that if you are using the query in a programming tool, this is not what you want to do. You want the raw date value, for display purposes, there will be a similar formatting function from whatever programming environment you use.

As you can see from the reference in DATE_FORMAT, you will want to use '%d-%m-%Y', e.g.

SELECT col1, col2, DATE_FORMAT(datecolumn, '%d-%m-%Y') AS datecolumn, more1...
FROM sometable
....
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
5

No, it's not possible. But you can use DATE_FORMAT to select that way.

SELECT Date_format(mydatefield, '%d-%m-%Y') 
FROM   table
Pentium10
  • 204,586
  • 122
  • 423
  • 502
2

No, it's not possible while keeping it as a date field. I suggest you keep it in that format - so you can use all mysql date functions - and change it only when you display it to the users.

You can either do it application side or directly with a query:

SELECT DATE_FORMAT(date_field, "%d-%m-%Y") FROM ...
Matteo Riva
  • 24,728
  • 12
  • 72
  • 104
  • I have another way, I can store int type timestamp by strtotime() then it can be utilized with date() function. i ain't sure it's is good way. – SohailRajput Mar 20 '11 at 09:51
  • 1
    Yes but an int timestamp is totally unreadable. The mysql default format lets you understand the date without processing it. Also you can't use date functions (to extract day, month, intervals, etc.) if you use INTs – Matteo Riva Mar 20 '11 at 09:52
0

You can do this using SQL

SELECT DATE_FORMAT(date_field, "%d-%m-%Y") FROM ...

Or if you're using PHP you can achieve the same results:

echo date('d-m-Y', $originalDate);  //You will get something like 09-08-2013 (Aug 8th, 2013)
echo date('j-n-y', $originalDate);  //You will get something like 9-8-13 (Aug 8th, 2013)
Sandy
  • 2,572
  • 7
  • 40
  • 61
0

I see the use for both, but I find this layout more useful as a reference tool:

SELECT DATE_FORMAT('2004-01-20' ,'%Y-%m-01');

enter image description here

manish1706
  • 1,571
  • 24
  • 22