MySQL Bugs: #58235: GET_FORMAT(DATETIME,'EUR') '%Y-%m-%d %H.%i.%s'

Bug #58235	GET_FORMAT(DATETIME,'EUR') '%Y-%m-%d %H.%i.%s'
Submitted:	16 Nov 2010 16:36	Modified:	17 Nov 2010 4:25
Reporter:	Manuel Braun	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1.41, 5.1.54	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	'EUR') wrong delivery, GET_FORMAT(DATETIME

Description:
GET_FORMAT(DATETIME,'EUR') delivers '%Y-%m-%d %H.%i.%s' that seems to be not quiet right. 

GET_FORMAT(DATE,'EUR') delivers the right format '%d.%m.%Y'

If you lookup the DIN 5008 you get %Y-%m-%d as format and as optional format %d.%m.%Y . Fact is that %Y-%m-%d is not enforced and the format d.%m.%Y is more often used. (http://de.wikipedia.org/wiki/Datumsformat)
Even if both are right, the same format should be used for Date and Datetime. 

GET_FORMAT(TIME,'EUR') delivers '%H.%i.%s' that seems to wrong
The used timeformat in Europe is normaly %h:%i:%s 

How to repeat:
Use the function GET_FORMAT
or lookup in the docs
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

Suggested fix:
The right term should be 
'%d.%m.%Y %H:%i:%s' 
or 
'%Y-%m-%d %H:%i:%s' but if used GET_FORMAT(DATE,'EUR') should be '%Y-%m-%d'

Indeed, this is what we have:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.54-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select GET_FORMAT(DATETIME,'EUR');
+----------------------------+
| GET_FORMAT(DATETIME,'EUR') |
+----------------------------+
| %Y-%m-%d %H.%i.%s          |
+----------------------------+
1 row in set (0.00 sec)

mysql> select GET_FORMAT(DATE,'EUR');
+------------------------+
| GET_FORMAT(DATE,'EUR') |
+------------------------+
| %d.%m.%Y               |
+------------------------+
1 row in set (0.00 sec)

mysql> select GET_FORMAT(TIME,'EUR');
+------------------------+
| GET_FORMAT(TIME,'EUR') |
+------------------------+
| %H.%i.%s               |
+------------------------+
1 row in set (0.00 sec)

The results of GET_FORMAT(DATETIME) and GET_FORMAT(DATE) are inconsistent in any case.

Actually this is explicitly documented at http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_get-format

There is no reference to any DIN for EUR, so formally this is not a bug.