Date and Time
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? Thanks, Donovan -- D Brooke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Date and Time
What's your problem/reason with how it is? Andy On Sun, Jan 8, 2012 at 8:21 PM, Donovan Brooke li...@euca.us 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.htmlhttp://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? Thanks, Donovan -- D Brooke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Date and Time
On 1/8/2012 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? As the manual says, MySQL wants -mm-dd. Use Str_To_Date() to format date strings to the format MySQL uses. PB - Thanks, Donovan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Date and Time
Peter Brawley wrote: On 1/8/2012 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? As the manual says, MySQL wants -mm-dd. Use Str_To_Date() to format date strings to the format MySQL uses. PB - Thanks! Donovan -- D Brooke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Date and Time
What's your problem/reason with how it is? I assume Andy means: leave it stored as a timestamp type or datetime type, and when you need to display it otherwise.. then covert with date() -G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Date and Time
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
Re: Date and Time
leave it stored as a timestamp type or datetime type, and when you need to display it otherwise.. then covert with date() oops, Paul's post reminded me I was suggesting a PHP function here ^^^ ... and this is the MySQL list. -G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql