On Jan 8, 2012, at 2:21 PM, Donovan Brooke wrote:
> Hello, I'm doing an insert into with date and time type fields.
>
> I was reading:
> http://dev.mysql.com/doc/refman/5.1/en/date-and-time-literals.html
>
> My question is: is the format always 'year month day'?.. or can we save dates in
> 'month day year' as well?
In DATE, DATETIME, and TIMESTAMP columns, you must specify the date part on year-month-day
order.
If you want to store a value in a different format, you must use some other data type such
as VARCHAR. But then it won't be interpreted as a date.
If you want to display a date from a DATE, etc. column in some other format, pass the
value to DATE_FORMAT().
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format
If you want to reformat a date value in some other format to put it in year-month-day
format so that you can store it in a DATE, etc. column, STR_TO_DATE() might be helpful.
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date
STR_TO_DATE() can be useful, for example, when loading non year-month-day data into a
table with LOAD DATA. You can use STR_TO_DATE() to reformat the values on the fly.
LOAD DATA LOCAL INFILE 'data.txt'
INTO TABLE t (name,@date,value)
SET date = STR_TO_DATE(@date,'%m/%d/%y');
--
Paul DuBois
Oracle Corporation / MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.com
