Bug #3852 support for various datetime formats v4.1 and up
Submitted: 21 May 2004 20:39 Modified: 21 May 2004 22:34
Reporter: Gregory Z Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version: OS:Any (any)
Assigned to: CPU Architecture:Any

[21 May 2004 20:39] Gregory Z 
Description:
MySQL Server at least up to v3.23.xx supported US format for entry of datetime (mm/dd/yy hh:mm:ss) along with standard format(yy-mm-dd hh:mm:ss). Then v4.1 stopped supporting US format leaving only standard.

While I agree that restricting format is a good thing, it's hard to migrate to newer versions since all the client apps that used US format require modifications.
In particular it made our lives much harder since we are in environment dominated by Sybase and we want ability to issue the same query to Sybase/Mysql and get the same resutls.

How to repeat:
insert datetime column or specify condition on datetime column in US format(mm/dd/yy hh:mm:ss)

Suggested fix:
datetime_format variable already exists (it can be seen in winmysqladmin and probably other places that I don't know of. It is set to %Y-%m-%d %H:%i:%s and I was hoping that changing it to %m/%d/%Y %H:%i:%s would to the trick for me.

I suggest actually implementing this type of functionality.
[21 May 2004 20:53] Paul DuBois 
The initial statement in this feature request is not correct.
MySQL has never supported date entry in any format
other than year-month-date.  A date in mm/dd/yy format
might have been accepted, but it would have been intrepreted
with mm as the year, dd as the month, and yy as the day.
[21 May 2004 21:20] Gregory Z 
I stand corrected. it was not there - just confirmed.

Is still it possible to have this concern addressed in future releases?
[21 May 2004 22:34] Sergei Golubchik 
Yes, definitely.

It was even implemented already, but because of some problems with the implementation it went into the source tree only partially.

But now in 4.1 you can issue STR_TO_DATE(str, format) to convert any string to a date.
And GET_FORMAT() function to get format string for USA dates. See the manual for these functions.