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]
>
