RE: DATE_FORMAT parameter question

2010-04-09 Thread Martin Gainty 

Hi Carsten

 

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

I will DEFINITELY check this out

 

Takk!
Martin Gainty 
__ 
Note de déni et de confidentialité
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.



 

> Date: Fri, 9 Apr 2010 19:02:33 +0200
> From: cars...@bitbybit.dk
> To: mgai...@hotmail.com
> CC: mysql@lists.mysql.com
> Subject: 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-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 
> > __ 
> > 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 res

Re: DATE_FORMAT parameter question

2010-04-09 Thread Carsten Pedersen 
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-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 
__ 
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=arch...@jab.org

RE: DATE_FORMAT parameter question

2010-04-09 Thread Martin Gainty 

(Agradecimentos para o conselho)

Eu espero que o homem responsável parará a chuva e permitirá a todos nós a 
oportunidade de dessecar e apreciar a luz do sol

Obrigado!
Martin Gainty 
__ 
Por favor não altere/interrompa ou altere esta transmissão



> To: mysql@lists.mysql.com
> From: j...@consultorweb.cnt.br
> Subject: Re: DATE_FORMAT parameter question
> Date: Fri, 9 Apr 2010 12:56:46 -0300
> 
> Basicay, your date_format works like:
> 
> select DATE_FORMAT('YY-MM-DD','%y-%b-%d') from DUAL;
> 
> "Martin Gainty"  escreveu na mensagem 
> news:blu142-w2137936b18ae273dbd6cb1ae...@phx.gbl...
> 
> 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
> __
> 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=mgai...@hotmail.com
> 
  
_
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

Re: DATE_FORMAT parameter question

2010-04-09 Thread 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"  escreveu na mensagem 
news:blu142-w2137936b18ae273dbd6cb1ae...@phx.gbl...

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
__
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=arch...@jab.org

Re: Date_Format

2004-02-20 Thread David Griffiths 

If you need the date like, '2004/01/01', then shouldn't it be '%Y/%m/%d'?

You still ahve the dashes (the '-' character) in your date-format, and you
need a forward-slash (the '/' character) instead.

Here's what I ran on MySQL:

mysql> select date_format(NOW(), '%Y/%m/%d');
++
| date_format(NOW(), '%Y/%m/%d') |
++
| 2004/02/20 |
++
1 row in set (0.00 sec)


David
- Original Message -
From: "Jacque Scott" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, February 20, 2004 10:31 AM
Subject: Date_Format


> I have the following query where the user will put in a date.  They will
> put it in like '01/01/04'.  I use this date in my WHERE clause but MySQL
> needs the date like '2004/01/01'.  I tried using Date_Format('04/01/01',
> '%Y-%m-%d') to change the way it is formatted but I don't get the
> correct data back.  Can someone help with this?
>
> SELECT DISTINCTROW Products.NSIPartNumber, Sum(tblInvTransaction.Qty)
> AS SumOfQty
> FROM tblInvTransaction INNER JOIN Products ON
> tblInvTransaction.ProductID = Products.ProductId
> WHERE tblInvTransaction.Date <= Date_Format('01/01/04', '%Y-%m-%d')
> GROUP BY Products.NSIPartNumber
> HAVING Sum(tblInvTransaction.Qty)>0;
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: date_format function - German

2003-10-22 Thread Henning Heil 
Hello Steve,

 Steve Vernon wrote on 22.10.2003 16:47 

I suppose I could return the month as a number, and then output the text
from that, but this seems a bit tacky!
I think that's the most reliable way, imagine you move to a new server 
or install your software somewhere else . . . I got used to passing 
unix_time or timestamps to my applications and modify them there, not 
nice but it works. In php you can use setlocale for time and date 
functions to get correct values, there might be sth similar in java etc.

rgds,

Henning



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: date_format function - German

2003-10-22 Thread Bernhard Döbler 
Look in your my.ini file.

There's maybe an entry:
language=f:/mysql/share/german

Set it to your favourit language!

Bernhard

- Original Message - 
From: "Steve Vernon" <[EMAIL PROTECTED]>
To: "Mysql List" <[EMAIL PROTECTED]>
Sent: Wednesday, October 22, 2003 4:47 PM
Subject: date_format function - German


> I  have read and searched the internet, but I cannot find much on getting
> mysql to return German and other languages months. Is there codes you use?
> 
> I really would prefer to use the English MySQL (if there is such a one) as I
> don't know much German, and need the error messages in English!
> 
> I suppose I could return the month as a number, and then output the text
> from that, but this seems a bit tacky!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: DATE_FORMAT PROBLEM

2002-10-03 Thread Keith C. Ivey 

On 3 Oct 2002, at 15:33, Mark Colvin wrote:

> The problem I am having is with the DATE_FORMAT. Running the above query
> returns the following for the ‘width’ column.
>
> 94.40 DATE_FORMAT(d1.created, '%d-%m-%Y'): NULL DATE_FORMAT(d1.amended,
> '%d-%m-%Y'): 03-10-2002

Show your PHP code.  How exactly are you retrieving the values?  This
doesn't seem like a problem with MySQL itself but rather with PHP or
(much more likely) with your code.

--
Keith C. Ivey <[EMAIL PROTECTED]>
Tobacco Documents Online
http://tobaccodocuments.org

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: date_format question

2002-05-22 Thread Gurhan Ozen 

Yes, there is .. You can use %D (note that it is capital D) instead of %e to
get what you wanna get.

See: http://www.mysql.com/doc/D/a/Date_and_time_functions.html

Gurhan


-Original Message-
From: Lee P Reilly [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 22, 2002 5:51 PM
To: MySQL
Subject: date_format question


Hi,

I have a quick question concerning date/time functions. If I query
MySQL:
   SELECT date_format(date, '%W %M %e %Y') FROM experiments where
expid=3706
  It returns: Wednesday May 2 1979

Are there existing methods for determining whether to add "st", "nd" or
"rd" after the %e?
e.g. in this case, it would instead read:
   Wednesday May 2nd 1979

Thanks very much in advance for any help.

- Best regards,

Lee Reilly


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: date_format

2001-12-31 Thread Admin 

Julian,

The "as" field is just setting the name of the field or column. For example
in PHP, if you don´t use the "as somename", you must use array[2] (I just
use 2 as an example) instead of array['datefield'] to get the information.

Regards,

Ingvar G
Web accounting & CRM
us.logiledger.com

- Original Message -
From: "julian haffegee" <[EMAIL PROTECTED]>
To: "MySQL General List" <[EMAIL PROTECTED]>
Sent: Monday, December 31, 2001 2:55 PM
Subject: Re: date_format


> Thanks this works
>
> the difference was the 'AS datefield'
> what is this bit doing? I searched for AS (in the SELECT description in my
> book) but it does not mention it; though I have seen it in examples. I
like
> to understand why I use the code I do...
>
> I looked at http://www.mysql.com/ but can find no mention. Am I searching
> for the wrong thing? I searched for AS in the documentation
> thanks again Jules
>
>
> >
> > You may want to try:
> >
> > $result = mysql_query("SELECT title, description, url, author,
> > date_format(datefield, '%M %D %Y') AS datefield FROM documents");
> >
>
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: date_format

2001-12-31 Thread julian haffegee 

Thanks this works

the difference was the 'AS datefield'
what is this bit doing? I searched for AS (in the SELECT description in my
book) but it does not mention it; though I have seen it in examples. I like
to understand why I use the code I do...

I looked at http://www.mysql.com/ but can find no mention. Am I searching
for the wrong thing? I searched for AS in the documentation
thanks again Jules


>
> You may want to try:
>
> $result = mysql_query("SELECT title, description, url, author,
> date_format(datefield, '%M %D %Y') AS datefield FROM documents");
>



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: date_format

2001-12-30 Thread Chris Bolt 

> #2 All I have read on the web/books suggests that this is what to do
>   $result = mysql_query ("SELECT title, description, url, author,
> date_format(datefield, %M %D %Y')   FROM documents");
>
> This just leaves out all dates.

You may want to try:

$result = mysql_query("SELECT title, description, url, author,
date_format(datefield, '%M %D %Y') AS datefield FROM documents");

You can also do

$result = mysql_query("SELECT title, description, url, author,
unix_timestamp(datefield) AS datefield FROM documents");

then use php's date() function.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: date_format doesn't work correct

2001-07-19 Thread Florian Schmidt 

At 17:51 19.07.2001 +0200, you wrote:

> >>okay, i see this output on my sco-machine:
> >>'2001071614' '2001-07-19 14:00:00'
> >>
> >>if i try the same with mysql-front on my win2k machine:
> >>'2001071914' '2001-07-19 14:00:00'
>
>
>Somehow your sample data doesn't look right: shouldn't it be
>
> >>'2001071614' '2001-07-16 14:00:00'
>  ^

exactly this is the bug!

the date at this time was: '2001-07-19 14:00:00' and now() correctly 
reports this, but a date_format(now(), '%Y%m%d%H') says '2001071614' with 
16 as the month-day instead of 19 which would be right !?

-- 

Florian Schmidt
mailto:[EMAIL PROTECTED]
http://www.f-24.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: date_format doesn't work correct

2001-07-19 Thread Florian Schmidt 

At 09:40 19.07.2001 -0400, you wrote:
>You're correct, likely not a timezone problem.
>
>Have you confirmed that the clocks on these machines are
>synchronized?  That they aren't really three days off?

yep, again and agein :)

the clocks are in sync +/- 5 secs

> >okay, i see this output on my sco-machine:
> >'2001071614' '2001-07-19 14:00:00'
> >
> >if i try the same with mysql-front on my win2k machine:
> >'2001071914' '2001-07-19 14:00:00'
> >
> >?!?
> >i'm using the C-API (libmysqlclient.a) from
> >mysql-3.23.38-pc-sco3.2v5.0.6-i386.tar.gz
>


Florian Schmidt
mailto:[EMAIL PROTECTED]
http://www.f-24.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: date_format doesn't work correct

2001-07-19 Thread Florian Schmidt 

At 09:25 19.07.2001 -0400, you wrote:
>Probably due to timezone setting differences between the two
>client machines.

i also thought about that, but there are 2 DAYs difference... can this be a 
timezone issue ?


>okay, i see this output on my sco-machine:
>'2001071614' '2001-07-19 14:00:00'
>
>if i try the same with mysql-front on my win2k machine:
>'2001071914' '2001-07-19 14:00:00'
>
>?!?
>i'm using the C-API (libmysqlclient.a) from
>mysql-3.23.38-pc-sco3.2v5.0.6-i386.tar.gz

-- 

Florian Schmidt
mailto:[EMAIL PROTECTED]
http://www.f-24.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: date_format sets time to 12:00:00

2001-05-19 Thread Sinisa Milivojevic 

Paul Schreiber writes:
> Sinisa Milivojevic wrote:
 
> 
> Column start is of type TIME. The actual values are illustrated above in 
> the example.
> 
> Paul
> 


Hi!

Then it is not a bug. As our manual clearly points out, date_format
can be used only on date and datetime column types.


Regards,

Sinisa

    __ _   _  ___ ==  MySQL AB
 /*/\*\/\*\   /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/   \*\_   |*|   |*||*| mailto:[EMAIL PROTECTED]
   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*| Larnaca, Cyprus
  /*/ /*/  /*/\*\_/*/ \*\_/*/ |*|
  /*/^^^\*\^^^
 /*/ \*\Developers Team

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: date_format sets time to 12:00:00

2001-05-18 Thread Paul Schreiber 

Sinisa Milivojevic wrote:

>[EMAIL PROTECTED] writes:
>> >Description:
>>  date_format is setting times to 12:00 AM; time_format works fine
>> >How-To-Repeat:
>> mysql> select start,TIME_FORMAT(start, '%l:%i %p') AS start2 FROM time;
>> +--+--+
>> | start| start2   |
>> +--+--+
>> | 12:00:00 | 12:00 PM |
>> | 14:00:00 | 2:00 PM  |
>> +--+--+
>> 2 rows in set (0.01 sec)
>> 
>> mysql> select start,DATE_FORMAT(start, '%l:%i %p') AS start2 FROM time;
>> +--+--+
>> | start| start2   |
>> +--+--+
>> | 12:00:00 | 12:00 AM |
>> | 14:00:00 | 12:00 AM |
>> +--+--+
>> 2 rows in set (0.06 sec)
> 
>
>
>Hi!
>
>And column start is of what type and what are it's actual values.

Column start is of type TIME. The actual values are illustrated above in 
the example.

Paul

 shad 96c / 4B CS / mac activist / eda / fumbler
fan of / jewel / sophie b. / sarah slean / steve poltz / emm gryner /
   / x-files / buffy / dawson's creek / habs / bills / 49ers /
 
 t h i n k  d i f f e r e n t.

"Mulder, not everything is a labyrinth of dark conspiracy, and not 
 everybody is plotting to deceive, inveigle, and obfuscate.
 ...
 Where are you going?"
 -- Dana Scully, "The X-Files"

"To find someone who I know plotted to deceive, inveigle, and obfuscate."
 -- Fox Mulder, "The X-Files"


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: date_format sets time to 12:00:00

2001-05-18 Thread Sinisa Milivojevic 

[EMAIL PROTECTED] writes:
> >Description:
>   date_format is setting times to 12:00 AM; time_format works fine
> >How-To-Repeat:
> mysql> select start,TIME_FORMAT(start, '%l:%i %p') AS start2 FROM time;
> +--+--+
> | start| start2   |
> +--+--+
> | 12:00:00 | 12:00 PM |
> | 14:00:00 | 2:00 PM  |
> +--+--+
> 2 rows in set (0.01 sec)
> 
> mysql> select start,DATE_FORMAT(start, '%l:%i %p') AS start2 FROM time;
> +--+--+
> | start| start2   |
> +--+--+
> | 12:00:00 | 12:00 AM |
> | 14:00:00 | 12:00 AM |
> +--+--+
> 2 rows in set (0.06 sec)
 


Hi!

And column start is of what type and what are it's actual values.


Regards,

Sinisa

    __ _   _  ___ ==  MySQL AB
 /*/\*\/\*\   /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/   \*\_   |*|   |*||*| mailto:[EMAIL PROTECTED]
   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*| Larnaca, Cyprus
  /*/ /*/  /*/\*\_/*/ \*\_/*/ |*|
  /*/^^^\*\^^^
 /*/ \*\Developers Team

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Date_format -> can't get it..

2001-04-10 Thread David Bouw 

Hi Peter,

The collumn is a datatime... You are tight that date probably should be is a
reserved word..
I must say that I never had any troubles with it, I started using it when I
was a newbie with PHP about a year ago...
On almost all my tables I always have an auto-increment column with the name
ID and a datetime column named DATE..

But you are right that it is probably reserved for some stuff..

The DATE_FORMAT can indeed be left out, but I once understood that this was
more save to do..???
I never asked why, but also never had any problems..

I found the source of my problem.. The table in which I didn't get the right
results was quite large..
About 3000 records..  The reason I knew that there was a mistake, was that
there was a (new) product which we bought about 50 of.. When I looked the
number sold + still in stock I couldn't get add up to 50..

It seemed that we added some code in which the current-time-stamp wasn't
stored with some orders with this product This way some of these
products weren't counted because they had the timestampt -00-00
00:00:00..

Founded this out when I was looking throught the table..

Thanks for the help..

Bye Bye
David



> First off, I don't think you should use 'date' as a column name; isn't it
> a reserved word?
>
> Then, hmm.. is that column a DATE column, or DATETIME, or TIMESTAMP?
> If it is DATETIME or TIMESTAMP, then you don't really need the
DATE_FORMAT()
> conversion; compaing a DATETIME or TIMESTAMP value directly to a
DATE_SUB()
> return value works perfectly well.
>
> Actually, I just checked it, and comparing a DATE value against a
DATE_SUB()
> return value works just as well; so my advice is, lose the DATE_FORMAT(),
> try field >= DATE_SUB(NOW(), INTERVAL 30 DAY) directly.
>
> G'luck,
> Peter
>
> --
> If I were you, who would be reading this sentence?
>
> On Tue, Apr 10, 2001 at 09:47:40AM +0200, David Bouw wrote:
> > Hi there,
> >
> > I don't seem to understand the DATE_SUB / INTERVAL function..
> >
> > I have a query which basically retrieves data from a table which
consists of
> > ordernumers and quantities..
> > In the following example everything of ordernr 'te-004' should be
retrieved,
> > quantities counted and this should be done only for the last 30 days..
> > When I remove the pieces which tries to get the last 30 days everything
> > works fine (except that everything in the database is counted..) When I
add
> > the date 'piece' I can't seem to figure out what goes wrong..
> > In any case I don't get the result I expect. (Not everything is
counted..)
> >
> > SELECT perfect_customer_productorders.ordernr,
> > Sum(perfect_customer_productorders.quantity) AS quantity FROM
> > perfect_customer_productorders left join perfect_customer_orders on
> > perfect_customer_productorders.orderid = perfect_customer_orders.id
where
> > ((perfect_customer_productorders.date) >= DATE_FORMAT( DATE_SUB( NOW() ,
> > INTERVAL 30 DAY ) , '%Y-%m-%d' ) and
perfect_customer_productorders.ordernr
> > = 'te-004' and perfect_customer_orders.status != 2) GROUP BY
> > perfect_customer_productorders.ordernr ORDER BY quantity desc
> >
> > -> This is the trouble maker: (perfect_customer_productorders.date) >=
> > DATE_FORMAT( DATE_SUB( NOW() , INTERVAL 30 DAY ) , '%Y-%m-%d' )
> >
> > My question: How do I get an date which is 30 days back from now..?
> > I have PHP, so I could basically also use mktime to generate a date 30
days
> > back.. But I think it's nicer to do this in MySQL
> >
> > Thanks in advance for all the help..
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Date_format -> can't get it..

2001-04-10 Thread Peter Pentchev 

First off, I don't think you should use 'date' as a column name; isn't it
a reserved word?

Then, hmm.. is that column a DATE column, or DATETIME, or TIMESTAMP?
If it is DATETIME or TIMESTAMP, then you don't really need the DATE_FORMAT()
conversion; compaing a DATETIME or TIMESTAMP value directly to a DATE_SUB()
return value works perfectly well.

Actually, I just checked it, and comparing a DATE value against a DATE_SUB()
return value works just as well; so my advice is, lose the DATE_FORMAT(),
try field >= DATE_SUB(NOW(), INTERVAL 30 DAY) directly.

G'luck,
Peter

-- 
If I were you, who would be reading this sentence?

On Tue, Apr 10, 2001 at 09:47:40AM +0200, David Bouw wrote:
> Hi there,
> 
> I don't seem to understand the DATE_SUB / INTERVAL function..
> 
> I have a query which basically retrieves data from a table which consists of
> ordernumers and quantities..
> In the following example everything of ordernr 'te-004' should be retrieved,
> quantities counted and this should be done only for the last 30 days..
> When I remove the pieces which tries to get the last 30 days everything
> works fine (except that everything in the database is counted..) When I add
> the date 'piece' I can't seem to figure out what goes wrong..
> In any case I don't get the result I expect. (Not everything is counted..)
> 
> SELECT perfect_customer_productorders.ordernr,
> Sum(perfect_customer_productorders.quantity) AS quantity FROM
> perfect_customer_productorders left join perfect_customer_orders on
> perfect_customer_productorders.orderid = perfect_customer_orders.id where
> ((perfect_customer_productorders.date) >= DATE_FORMAT( DATE_SUB( NOW() ,
> INTERVAL 30 DAY ) , '%Y-%m-%d' ) and perfect_customer_productorders.ordernr
> = 'te-004' and perfect_customer_orders.status != 2) GROUP BY
> perfect_customer_productorders.ordernr ORDER BY quantity desc
> 
> -> This is the trouble maker: (perfect_customer_productorders.date) >=
> DATE_FORMAT( DATE_SUB( NOW() , INTERVAL 30 DAY ) , '%Y-%m-%d' )
> 
> My question: How do I get an date which is 30 days back from now..?
> I have PHP, so I could basically also use mktime to generate a date 30 days
> back.. But I think it's nicer to do this in MySQL
> 
> Thanks in advance for all the help..

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: date_format

2001-01-17 Thread Scott Gerhardt 

Hello Cindy,

I posted this in response to another date formatting question, hope it
helps.
The link should be helpful if you haven't found it already.

SELECT fields, DATE_FORMAT(datefield, '%M %d, %Y');

I just looked it up myself...It's all in the manual at:
http://www.mysql.com/doc/D/a/Date_and_time_functions.html


Regards,


___

Scott A. Gerhardt  P.Geo.
Gerhardt Information Technologies
[EMAIL PROTECTED]
___



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: date_format

2001-01-17 Thread Cindy 


[EMAIL PROTECTED] writes:
 >It's (sort of) obvious what Cindy is looking for...
 >
 >In slightly clearer terms, is it possible to format a selected date using
 >a
 >format string similar to that available in C's strftime() function -
 >where %M might stand for month name, %d might stand for day number,
 >etc.

I would really like a complete list of the available %'s in this
function.  I thank you all for your help, but I still think that
entering DATE_FORMAT in the documentation search box at www.mysql.com
should have been successful.

(I'm specifically looking for a way to display Dec for 12, Jan for 01,
etc, but I want the complete list for future reference.)

--Cindy

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: date_format

2001-01-17 Thread Mikel King 

Hi Cindy,

   This might help... http://www.upan.org/rtfm/strftime.html

Cheers,
Mikel

Cindy wrote:

> OK.  I went to the documentation at www.mysql.com and typed in
> DATE_FORMAT in the search box, hopefully find a list of the %M's, etc
> rules that may be used (I'd like December to become Dec, etc).  No
> dice.  Randomly looking through sections that came up under "DATE"
> search didn't yield any other info on DATE_FORMAT.  20 minutes later,
> I surrender.  Where would I find out the info on DATE_FORMAT I'm
> looking for?
>
> --Cindy
> --
> [EMAIL PROTECTED]
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: date_format

2001-01-17 Thread skip 

It's (sort of) obvious what Cindy is looking for...

In slightly clearer terms, is it possible to format a selected date using
a
format string similar to that available in C's strftime() function -
where %M might stand for month name, %d might stand for day number,
etc.

regards,

P


On Wed, 17 Jan 2001, Pavel Kveton wrote:

> On Wednesday 17 January 2001 00:20 Cindy wrote:
> > OK.  I went to the documentation at www.mysql.com and typed in
> > DATE_FORMAT in the search box, hopefully find a list of the %M's, etc
> > rules that may be used (I'd like December to become Dec, etc).  No
> > dice.  Randomly looking through sections that came up under "DATE"
> > search didn't yield any other info on DATE_FORMAT.  20 minutes later,
> > I surrender.  Where would I find out the info on DATE_FORMAT I'm
> > looking for?
> 
> PLEASE, specify what do you want to do? Search the tables in database, import 
> data or what? "DATE" structures in mysql tables use just numeral format, as 
> far as I know...
> 
> Pavel
> 
> >
> > --Cindy
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: date_format

2001-01-17 Thread Pavel Kveton 

On Wednesday 17 January 2001 00:20 Cindy wrote:
> OK.  I went to the documentation at www.mysql.com and typed in
> DATE_FORMAT in the search box, hopefully find a list of the %M's, etc
> rules that may be used (I'd like December to become Dec, etc).  No
> dice.  Randomly looking through sections that came up under "DATE"
> search didn't yield any other info on DATE_FORMAT.  20 minutes later,
> I surrender.  Where would I find out the info on DATE_FORMAT I'm
> looking for?

PLEASE, specify what do you want to do? Search the tables in database, import 
data or what? "DATE" structures in mysql tables use just numeral format, as 
far as I know...

Pavel

>
> --Cindy

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: date_format

2001-01-16 Thread Matt Wagner 

Cindy writes:
> 
> OK.  I went to the documentation at www.mysql.com and typed in
> DATE_FORMAT in the search box, hopefully find a list of the %M's, etc
> rules that may be used (I'd like December to become Dec, etc).  No
> dice.  Randomly looking through sections that came up under "DATE"
> search didn't yield any other info on DATE_FORMAT.  20 minutes later,
> I surrender.  Where would I find out the info on DATE_FORMAT I'm
> looking for?

Hi!


There are 2 search methods on our website. There is the site-wide
search that appears in the upper left corner of the front page; and
then there is the localized search of the commented manual.

I'm assuming you used the site-wide search... I can't understand why
it is producing no results for 'DATE_FORMAT'. I will look into it.

However, when I tried your search query in the localized commented
manual search (http://www.mysql.com/doc/), the correct item came in
as the second search result.

   http://www.mysql.com/doc/D/a/Date_and_time_functions.html

So my suggestion to you is to use the commented manual
search. Meanwhile I will try to figure out why UdmSearch (site-wide)
is not working like it should.


Regards,

Matt

-- 
   __  ___ ___   __ 
  /  |/  /_ __/ __/ __ \/ /   Matt Wagner <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, http://www.mysql.com/
/_/  /_/\_, /___/\___\_\___/  River Falls, Wisconsin, USA
   <___/   Developer


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: date_format

2001-01-16 Thread Jason Brooke 

Try the manual contents, linked from the documentation page

http://www.mysql.com/documentation/mysql/bychapter


> OK.  I went to the documentation at www.mysql.com and typed in
> DATE_FORMAT in the search box, hopefully find a list of the %M's, etc
> rules that may be used (I'd like December to become Dec, etc).  No
> dice.  Randomly looking through sections that came up under "DATE"
> search didn't yield any other info on DATE_FORMAT.  20 minutes later,
> I surrender.  Where would I find out the info on DATE_FORMAT I'm
> looking for?
>
> --Cindy
> --
> [EMAIL PROTECTED]





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php