Re: Correct date query syntax

[SGreen]

I believe you are not letting MySQL do enough work for you.  The date 
format is perfect, even as a string, to perform the comparison you are 
trying to perform.

SELECT @currTime := NOW();
+-+
| @currTime := NOW()  |
+-+
| 2004-08-26 12:48:16 |
+-+
1 row in set (0.00 sec)

SELECT @currTime as CurrentTime
, (@currTime <= '2005-08-01')
, (@currTime > '2005-08-01')
, (@currTime > '2004-08-26 12:00:00')\G
*** 1. row ***
CurrentTime: 2004-08-26 12:48:16
(@currTime <= '2005-08-01'): 1
 (@currTime > '2005-08-01'): 0
(@currTime > '2004-08-26 12:00:00'): 1
1 row in set (0.00 sec)

As you can see, MySQL is perfectly capable of comparing dates and 
datetimes if you just let it. There is no reason to convert them to any 
sort of intermediate value as it will do it for you internally and 
automatically. Please read this to see just how many different ways Dates 
can be specified:

http://dev.mysql.com/doc/mysql/en/DATETIME.html



You could try this as your query:

SET @date1 ='2004-08-12', date2='2004-08-18';

SELECT * 
FROM account 
WHERE (date >= date1) 
AND (date <= date2);


and it should work just fine.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
 

"Yong Wang" <[EMAIL PROTECTED]> wrote on 08/26/2004 12:31:46 PM:

> Hi, All:
> I have a database which contains date attribute in string format
> (like 2004-08-12). I want to genearte a report based on period time.
> I use the syntax:
> date1 ='2004-08-12'
> date2='2004-08-18'
> SELECT * FROM account WHERE (TO_DAYS(date) >= TODAYS(date1)) and
> (TO_DAYS(date) <= TO_DAYS(date2));
> The report script complains the condition after WHERE clause. The
> reason I use TO_DAYS is that I want to convert 
> string date data into integer for comparison. Can I use TO_DAYS() like
> this way ?
>  Thanks a lot.
> 
> Yong
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

Re: Correct date query syntax

[Jochem van Dieten]

On Thu, 26 Aug 2004 11:31:46 -0500, Yong Wang <[EMAIL PROTECTED]> wrote:
> 
> I have a database which contains date attribute in string format
> (like 2004-08-12). I want to genearte a report based on period time.
> I use the syntax:
> date1 ='2004-08-12'
> date2='2004-08-18'
> SELECT * FROM account WHERE (TO_DAYS(date) >= TODAYS(date1)) and
> (TO_DAYS(date) <= TO_DAYS(date2));

Is "date" the name of your field? Change it, it is a reserved word in SQL.


> The report script complains the condition after WHERE clause. The
> reason I use TO_DAYS is that I want to convert
> string date data into integer for comparison. Can I use TO_DAYS() like
> this way ?


Even if you can, don't: it isn't needed.
Just use a plain BETWEEN predicate without functions:
SELECT *
FROM account
WHERE date BETWEEN date1 AND date2

Jochem

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