List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 9 2012 12:03am
Subject:Re: Date and Time
View as plain text  
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

Thread
Date and TimeDonovan Brooke8 Jan
  • Re: Date and TimeAndrew Moore8 Jan
    • Re: Date and TimeGovinda8 Jan
  • Re: Date and TimePeter Brawley8 Jan
    • Re: Date and TimeDonovan Brooke8 Jan
  • Re: Date and TimePaul DuBois9 Jan
    • Re: Date and TimeGovinda9 Jan