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:
> 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

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().

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.

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.

  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

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