Re: DATE_FORMAT parameter question

[João Cândido de Souza Neto]

Basicay, your date_format works like:

select DATE_FORMAT('YY-MM-DD','%y-%b-%d') from DUAL;

"Martin Gainty" <mgainty@stripped> escreveu na mensagem 
news:BLU142-W2137936B18AE273DBD6CB1AE150@ style="color:#666">stripped...

Good Afternoon All


following the documentation available at

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

i wish to select a date using DD-MON-YY format but my format of '%y-%b-%d' 
appears to be incorrect



mysql> select DATE_FORMAT('11-10-09','%y-%b-%d') from DUAL;
+------------------------------------+
| DATE_FORMAT('11-10-09','%y-%b-%d') |
+------------------------------------+
| 11-Oct-09                          |
+------------------------------------+
1 row in set (0.00 sec)



mysql> show variables like "%VERSION%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| protocol_version        | 10                           |
| version                 | 5.1.25-rc-community-log      |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | ia32                         |
| version_compile_os      | Win32                        |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)


it seems when i follow the documentation which states %d is used for day and 
%y is year i see:
mysql> select DATE_FORMAT('11-10-09','%d-%b-%y') from DUAL;
+------------------------------------+
| DATE_FORMAT('11-10-09','%d-%b-%y') |
+------------------------------------+
| 09-Oct-11                          |



but if i switch %y and %d in date_format I get the correct result e.g.

mysql> select DATE_FORMAT('11-10-09','%y-%b-%d') from DUAL;
+------------------------------------+
| DATE_FORMAT('11-10-09','%y-%b-%d') |
+------------------------------------+
| 11-Oct-09                          |
+------------------------------------+
1 row in set (0.00 sec)


any ideas on what I am doing wrong with format string to produce desired 
DD-MON-YY format?



Many Thanks,
Martin Gainty
______________________________________________

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene 
Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte 
Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht 
dient lediglich dem Austausch von Informationen und entfaltet keine 
rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von 
E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.




_________________________________________________________________
Hotmail is redefining busy with tools for the New Busy. Get more from your 
inbox.
http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_2