At 18:58 -0700 4/29/03, Scott Haneda wrote:
>on 04/29/2003 6:28 PM, Scott Haneda at lists@stripped wrote:
>
>> I have the following table transaction
>> +------------+---------------+------+-----+---------+----------------+
>> | Field | Type | Null | Key | Default | Extra |
>> +------------+---------------+------+-----+---------+----------------+
>> | id | int(11) | | PRI | NULL | auto_increment |
>> | user_id | int(11) | | | 0 | |
>> | amount | decimal(6,2) | | | 0.00 | |
>> | trans_type | varchar(12) | | | | |
>> | error | varchar(80) | | | | |
>> | added | timestamp(14) | YES | | NULL | |
>> +------------+---------------+------+-----+---------+----------------+
>>
>> I need to do a
>> Select SUM(amount) from transaction WHERE added ..... Is in todays data
>> range. Can someone help me with this one, thanks.
>
>I think I got it, I am not sure this is the best way...
>select SUM(amount) from transaction where added >= CONCAT(YEAR(NOW()), '-',
>MONTH(NOW()),'-', DAYOFMONTH(NOW()));
It's easier to use this:
WHERE FROM_DAYS(TO_DAYS(added)) = CURDATE()
TO_DAYS() converts added to days, chopping off the time part in the
process, and FROM_DAYS() converts the results back to a date. Then
you can simply compare it to CURDATE(), which is today's date.
>
>I had to CONCAT a '-' in there because the month and day are returned as
>single digits when they are less then 10, is there a way to do it perhaps
>more like...
>
>select SUM(amount) from transaction where added >= CONCAT(YEAR(NOW()) as
>INT(2), '-', MONTH(NOW()),'-', DAYOFMONTH(NOW()) as INT(2));
>
>Or something like that, excuse my guesswork syntax, I can not locate this in
>the manual, so I have been CONCAT'ing the '-' for the time being.
>
>-------------------------------------------------------------
>Scott Haneda Tel: 415.898.2602
>http://www.newgeo.com Fax: 313.557.5052
>scott@stripped Novato, CA U.S.A.
--
Paul DuBois
http://www.kitebird.com/
sql, query
