[ic] Customer Housekeeping & IC 4.8.7 - Resend
Mark Bryant
mark at vwe.net
Fri Mar 4 13:20:49 EST 2005
At 17:30 04/03/2005, you wrote:
>Mark Bryant wrote:
>
>>At 16:08 04/03/2005, you wrote:
>>
>>>>>This is untested...
>>>>>
>>>>>SELECT userdb.username from userdb LEFT JOIN transactions ON
>>>>>userdb.username=transactions.username WHERE transactions.code IS NULL
>>>>>AND FROM_UNIXTIME(mod_time) < SUBDATE (NOW(), INTERVAL 1 MONTH);
>>>>>||
>>>>>Dan
>>>>
>>>>
>>>>
>>>>
>>>>Hi Dan,
>>>>
>>>>Many thanks for the select :)
>>>>
>>>>Sadly it also fails in a similar way to the previous one and spews an
>>>>error about (NOW(), INTERVAL 1 MONTH) which would indicate it doesn't
>>>>like anything after < SUBDATE.
>>>>
>>>>I suspect the solution is to upgrade to a newer version of MySQL as
>>>>clearly 3.23 is lacking in a fair bit of functionality.
>>>>
>>>>Once again, many thanks for your help :)
>>>>
>>>>Mark
>>>Mark,
>>>
>>>I use MySQL 3.23.58 and I pasted the query above into phpMyAdmin and it
>>>ran fine. Do you have any more information about the error?
>>>
>>>Dan
>>
>>
>>Hi Dan,
>>
>>I'm using mysql-server-3.23.49-3 and the exact error given by webmin is:
>>
>>Failed to execute SQL : SQL SELECT userdb.username from userdb LEFT JOIN
>>transactions ON userdb.username=transactions.username WHERE
>>transactions.code IS NULL AND FROM_UNIXTIME(mod_time) < SUBDATE (NOW(),
>>INTERVAL 1 MONTH); failed : You have an error in your SQL syntax near
>>'(NOW(), INTERVAL 1 MONTH)' at line 1
>>
>>Thanks
>>
>>Mark
>Can you execute either of these queries?
>
>SELECT SUBDATE(NOW() , INTERVAL 1 MONTH);
>
>SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);
>
>They should both be available in your version of MySQL.
>
>Dan
Yes, both of those statements will run quite happily.
SELECT SUBDATE(NOW() , INTERVAL 1 MONTH);
SUBDATE(NOW() , INTERVAL 1 MONTH)
2005-02-04 18:19:03
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);
DATE_SUB(NOW(), INTERVAL 1 MONTH)
2005-02-04 18:17:47
Mark
Eros Shop
vwe internet ltd
PO BOX 1067
SLOUGH
SL1 7YA
UK
Shop - http://www.eros-shop.co.uk
EMail - info at eros-shop.co.uk
Tel - 0870 284 3369
Fax - 0870 284 4469
More information about the interchange-users
mailing list