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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
