DATE_FORMAT parameter question

--_2654a557-d8bf-43dd-b3c0-abbe8e0e63d3_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Good Afternoon All


following the documentation available at

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

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

=20

mysql> select DATE_FORMAT('11-10-09'=2C'%y-%b-%d') from DUAL=3B
+------------------------------------+
| DATE_FORMAT('11-10-09'=2C'%y-%b-%d') |
+------------------------------------+
| 11-Oct-09 |
+------------------------------------+
1 row in set (0.00 sec)

=20

mysql> show variables like "%VERSION%"=3B
+-------------------------+------------------------------+
| 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 an=
d %y is year i see:
mysql> select DATE_FORMAT('11-10-09'=2C'%d-%b-%y') from DUAL=3B
+------------------------------------+
| DATE_FORMAT('11-10-09'=2C'%d-%b-%y') |
+------------------------------------+
| 09-Oct-11 |

=20

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

mysql> select DATE_FORMAT('11-10-09'=2C'%y-%b-%d') from DUAL=3B
+------------------------------------+
| DATE_FORMAT('11-10-09'=2C'%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?

=20

Many Thanks=2C
Martin Gainty=20
______________________________________________=20
Verzicht und Vertraulichkeitanmerkung/Note de d=E9ni et de confidentialit=
=E9

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaeng=
er sein=2C so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiter=
leitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient l=
ediglich dem Austausch von Informationen und entfaltet keine rechtliche Bin=
dungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen w=
ir keine Haftung fuer den Inhalt uebernehmen.

Ce message est confidentiel et peut =EAtre privil=E9gi=E9. Si vous n'=EAtes=
pas le destinataire pr=E9vu=2C nous te demandons avec bont=E9 que pour sat=
isfaire informez l'exp=E9diteur. N'importe quelle diffusion non autoris=E9e=
ou la copie de ceci est interdite. Ce message sert =E0 l'information seule=
ment et n'aura pas n'importe quel effet l=E9galement obligatoire. =C9tant d=
onn=E9 que les email peuvent facilement =EAtre sujets =E0 la manipulation=
=2C nous ne pouvons accepter aucune responsabilit=E9 pour le contenu fourni=
..


=20
____________________________________________________________ _____
Hotmail is redefining busy with tools for the New Busy. Get more from your =
inbox.
http://www.windowslive.com/campaign/thenewbusy?ocid=3DPID283 26::T:WLMTAGL:O=
N:WL:en-US:WM_HMP:042010_2=

--_2654a557-d8bf-43dd-b3c0-abbe8e0e63d3_--

Re: DATE_FORMAT parameter question

Basicay, your date_format works like:

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

"Martin Gainty" escreveu na mensagem
news:BLU142-W2137936B18AE273DBD6CB1AE150@phx.gbl...

Good Afternoon All


following the documentation available at

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functio ns.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
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

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.

Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire
informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie
de ceci est interdite. Ce message sert à l'information seulement et n'aura
pas n'importe quel effet légalement obligatoire. Étant donné que les email
peuvent facilement être sujets à la manipulation, nous ne pouvons accepter
aucune responsabilité pour le contenu fourni.



____________________________________________________________ _____
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



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: DATE_FORMAT parameter question

--_a3f738e4-b0ba-4730-a456-9a231da0cd0e_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


(Agradecimentos para o conselho)

Eu espero que o homem respons=E1vel parar=E1 a chuva e permitir=E1 a todos =
n=F3s a oportunidade de dessecar e apreciar a luz do sol

Obrigado!
Martin Gainty=20
______________________________________________=20
Por favor n=E3o altere/interrompa ou altere esta transmiss=E3o



> To: mysql@lists.mysql.com
> From: joao@consultorweb.cnt.br
> Subject: Re: DATE_FORMAT parameter question
> Date: Fri=2C 9 Apr 2010 12:56:46 -0300
>=20
> Basicay=2C your date_format works like:
>=20
> select DATE_FORMAT('YY-MM-DD'=2C'%y-%b-%d') from DUAL=3B
>=20
> "Martin Gainty" escreveu na mensagem=20
> news:BLU142-W2137936B18AE273DBD6CB1AE150@phx.gbl...
>=20
> Good Afternoon All
>=20
>=20
> following the documentation available at
>=20
> http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functio ns.html
>=20
> i wish to select a date using DD-MON-YY format but my format of '%y-%b-%d=
'=20
> appears to be incorrect
>=20
>=20
>=20
> mysql> select DATE_FORMAT('11-10-09'=2C'%y-%b-%d') from DUAL=3B
> +------------------------------------+
> | DATE_FORMAT('11-10-09'=2C'%y-%b-%d') |
> +------------------------------------+
> | 11-Oct-09 |
> +------------------------------------+
> 1 row in set (0.00 sec)
>=20
>=20
>=20
> mysql> show variables like "%VERSION%"=3B
> +-------------------------+------------------------------+
> | 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)
>=20
>=20
> it seems when i follow the documentation which states %d is used for day =
and=20
> %y is year i see:
> mysql> select DATE_FORMAT('11-10-09'=2C'%d-%b-%y') from DUAL=3B
> +------------------------------------+
> | DATE_FORMAT('11-10-09'=2C'%d-%b-%y') |
> +------------------------------------+
> | 09-Oct-11 |
>=20
>=20
>=20
> but if i switch %y and %d in date_format I get the correct result e.g.
>=20
> mysql> select DATE_FORMAT('11-10-09'=2C'%y-%b-%d') from DUAL=3B
> +------------------------------------+
> | DATE_FORMAT('11-10-09'=2C'%y-%b-%d') |
> +------------------------------------+
> | 11-Oct-09 |
> +------------------------------------+
> 1 row in set (0.00 sec)
>=20
>=20
> any ideas on what I am doing wrong with format string to produce desired=
=20
> DD-MON-YY format?
>=20
>=20
>=20
> Many Thanks=2C
> Martin Gainty
> ______________________________________________
> Verzicht und Vertraulichkeitanmerkung/Note de d=E9ni et de confidentialit=
=E9
>=20
> Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene=20
> Empfaenger sein=2C so bitten wir hoeflich um eine Mitteilung. Jede unbefu=
gte=20
> Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht=
=20
> dient lediglich dem Austausch von Informationen und entfaltet keine=20
> rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von=20
> E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
>=20
> Ce message est confidentiel et peut =EAtre privil=E9gi=E9. Si vous n'=EAt=
es pas le=20
> destinataire pr=E9vu=2C nous te demandons avec bont=E9 que pour satisfair=
e=20
> informez l'exp=E9diteur. N'importe quelle diffusion non autoris=E9e ou la=
copie=20
> de ceci est interdite. Ce message sert =E0 l'information seulement et n'a=
ura=20
> pas n'importe quel effet l=E9galement obligatoire. =C9tant donn=E9 que le=
s email=20
> peuvent facilement =EAtre sujets =E0 la manipulation=2C nous ne pouvons a=
ccepter=20
> aucune responsabilit=E9 pour le contenu fourni.
>=20
>=20
>=20
> ____________________________________________________________ _____
> Hotmail is redefining busy with tools for the New Busy. Get more from you=
r=20
> inbox.
> http://www.windowslive.com/campaign/thenewbusy?ocid=3DPID283 26::T:WLMTAGL=
:ON:WL:en-US:WM_HMP:042010_2=20
>=20
>=20
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@hotmail.c=
om
>=20
=20
____________________________________________________________ _____
Hotmail is redefining busy with tools for the New Busy. Get more from your =
inbox.
http://www.windowslive.com/campaign/thenewbusy?ocid=3DPID283 26::T:WLMTAGL:O=
N:WL:en-US:WM_HMP:042010_2=

--_a3f738e4-b0ba-4730-a456-9a231da0cd0e_--

Re: DATE_FORMAT parameter question

If you'll excuse the shameless plug: I once created a tool to help find
the exact parameters to use for PHPs date() and MySQLs DATE_FORMAT().
Please see

http://bitbybit.dk/php/date_format/

(Yes, it looks horrible. But it works)

/ Carsten

Martin Gainty skrev:
> Good Afternoon All
>
>
> following the documentation available at
>
> http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functio ns.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
> ______________________________________________
> Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
>
> 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.
>
> Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni..
>
>
>
> ____________________________________________________________ _____
> 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
>
> !DSPAM:451,4bbf4251775757489286036!
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: DATE_FORMAT parameter question

--_5d1547f9-d15d-4407-b73f-77d0e354cde4_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Hi Carsten

=20

thanks for providing mysql support at 846pm kobenhavn time (on a fridag no =
less)

I will DEFINITELY check this out

=20

Takk!
Martin Gainty=20
______________________________________________=20
Note de d=E9ni et de confidentialit=E9
Ce message est confidentiel et peut =EAtre privil=E9gi=E9. Si vous n'=EAtes=
pas le destinataire pr=E9vu=2C nous te demandons avec bont=E9 que pour sat=
isfaire informez l'exp=E9diteur. N'importe quelle diffusion non autoris=E9e=
ou la copie de ceci est interdite. Ce message sert =E0 l'information seule=
ment et n'aura pas n'importe quel effet l=E9galement obligatoire. =C9tant d=
onn=E9 que les email peuvent facilement =EAtre sujets =E0 la manipulation=
=2C nous ne pouvons accepter aucune responsabilit=E9 pour le contenu fourni=
..



=20

> Date: Fri=2C 9 Apr 2010 19:02:33 +0200
> From: carsten@bitbybit.dk
> To: mgainty@hotmail.com
> CC: mysql@lists.mysql.com
> Subject: Re: DATE_FORMAT parameter question
>=20
> If you'll excuse the shameless plug: I once created a tool to help find=20
> the exact parameters to use for PHPs date() and MySQLs DATE_FORMAT().=20
> Please see
>=20
> http://bitbybit.dk/php/date_format/
>=20
> (Yes=2C it looks horrible. But it works)
>=20
> / Carsten
>=20
> Martin Gainty skrev:
> > Good Afternoon All
> >=20
> >=20
> > following the documentation available at
> >=20
> > http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functio ns.html
> >=20
> > i wish to select a date using DD-MON-YY format but my format of '%y-%b-=
%d' appears to be incorrect
> >=20
> >=20
> >=20
> > mysql> select DATE_FORMAT('11-10-09'=2C'%y-%b-%d') from DUAL=3B
> > +------------------------------------+
> > | DATE_FORMAT('11-10-09'=2C'%y-%b-%d') |
> > +------------------------------------+
> > | 11-Oct-09 |
> > +------------------------------------+
> > 1 row in set (0.00 sec)
> >=20
> >=20
> >=20
> > mysql> show variables like "%VERSION%"=3B
> > +-------------------------+------------------------------+
> > | 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)
> >=20
> >=20
> > it seems when i follow the documentation which states %d is used for da=
y and %y is year i see:
> > mysql> select DATE_FORMAT('11-10-09'=2C'%d-%b-%y') from DUAL=3B
> > +------------------------------------+
> > | DATE_FORMAT('11-10-09'=2C'%d-%b-%y') |
> > +------------------------------------+
> > | 09-Oct-11 |
> >=20
> >=20
> >=20
> > but if i switch %y and %d in date_format I get the correct result e.g.
> >=20
> > mysql> select DATE_FORMAT('11-10-09'=2C'%y-%b-%d') from DUAL=3B
> > +------------------------------------+
> > | DATE_FORMAT('11-10-09'=2C'%y-%b-%d') |
> > +------------------------------------+
> > | 11-Oct-09 |
> > +------------------------------------+
> > 1 row in set (0.00 sec)
> >=20
> >=20
> > any ideas on what I am doing wrong with format string to produce desire=
d DD-MON-YY format?
> >=20
> >=20
> >=20
> > Many Thanks=2C
> > Martin Gainty=20
> > ______________________________________________=20
> > Verzicht und Vertraulichkeitanmerkung/Note de d=E9ni et de confidential=
it=E9
> >=20
> > Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empf=
aenger sein=2C so bitten wir hoeflich um eine Mitteilung. Jede unbefugte We=
iterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht die=
nt lediglich dem Austausch von Informationen und entfaltet keine rechtliche=
Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koenn=
en wir keine Haftung fuer den Inhalt uebernehmen.
> >=20
> > Ce message est confidentiel et peut =EAtre privil=E9gi=E9. Si vous n'=
=EAtes pas le destinataire pr=E9vu=2C nous te demandons avec bont=E9 que po=
ur satisfaire informez l'exp=E9diteur. N'importe quelle diffusion non autor=
is=E9e ou la copie de ceci est interdite. Ce message sert =E0 l'information=
seulement et n'aura pas n'importe quel effet l=E9galement obligatoire. =C9=
tant donn=E9 que les email peuvent facilement =EAtre sujets =E0 la manipula=
tion=2C nous ne pouvons accepter aucune responsabilit=E9 pour le contenu fo=
urni..
> >=20
> >=20
> >=20
> > ____________________________________________________________ _____
> > Hotmail is redefining busy with tools for the New Busy. Get more from y=
our inbox.
> > http://www.windowslive.com/campaign/thenewbusy?ocid=3DPID283 26::T:WLMTA=
GL:ON:WL:en-US:WM_HMP:042010_2
> >=20
> > !DSPAM:451=2C4bbf4251775757489286036!
> >=20
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@hotmail.com
>=20
=20
____________________________________________________________ _____
The New Busy is not the too busy. Combine all your e-mail accounts with Hot=
mail.
http://www.windowslive.com/campaign/thenewbusy?tile=3Dmultia ccount&ocid=3DP=
ID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_4=

--_5d1547f9-d15d-4407-b73f-77d0e354cde4_--