[ic] Customer Housekeeping & IC 4.8.7 - Resend

[Dan Bergan]

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