[ic] Customer Housekeeping & IC 4.8.7 - Resend

Dan Bergan danb at championshipproductions.com
Fri Mar 4 13:50:37 EST 2005


Mark Bryant wrote:

> 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
>
It looks like the only difference between the earlier query and the one 
above is the space between SUBDATE and the parenthesis.  Maybe MySQL is 
choking on that space?  That seems unlikely, but if SUBDATE() runs fine, 
it should also work in the other query.

Dan



More information about the interchange-users mailing list