List Manager Support

List Manager support
Public write access is disabled. Please login

Filter View for future dates 16.04.2014 01:42


is it possible to create a view that shows a list with only future dates in it. Of course I hav a list with a date field.
I read something in another thread with the SQL query NOW() but I could not figure out how this works and where I have to put the query.

Thank you in advance for your advice.


Re: Filter View for future dates 16.04.2014 10:24

maybe you read about now function at table manager forum, as it allows direct sql expressions. In order to use such a filter at list manager, you will need to introduce a code tweak, because your expression NOW() is being quoted, so its handled as a text (which will never match your date field). Steps:

- Place at your view the filter condition yourdatefield 'More than' NOW()
- Introduce this line:

if($values_none=="'NOW()'") $values_none='NOW()';

at joomla\components\com_listmanager\models\serverpages.php

below db quote line (about line 407), so it will read

if($values_none=="'NOW()'") $values_none='NOW()';

It will execute now function as expected while keeping the rest of the text filters working as default.

Hope this helps,regards
Moonsoft Team

Re: Filter View for future dates 24.04.2014 00:14


thank you for your advice. I think I got an overview how this works. But I faced the problem, that I use german date format. So I had to convert to

if($values_none=="'NOW()'") $values_none= DATE_FORMAT(NOW(), '%d.%m.%Y');

But as I looked in the MySQL table I figured out that the date is saved as string. So a "greater than" for two dates in string format will not be successful. I have to convert it to datetime or something. Do you have an idea for that?

Re: Filter View for future dates 24.04.2014 09:55


If you want to convert date strings to mysql dates you should use str_to_date function. Please check examples at:

Maybe you should apply this function to 'v.value' in code and use now() without any conversion. Something like this:

if($values_none=="'NOW()'") $values_none='NOW()';


case 2: // More
$query .= " str_to_date(v.value,'%d,%m,%Y') > ".$values_none;
$query .= " v.value > ".$values_none;

Hope this helps. Regards,
Moonsoft Team

Re: Filter View for future dates 29.04.2014 00:16


I finally made it. Your code worked as suggested. I just had to replace "," with "." in the date format string.

$query .= " str_to_date(v.value,'%d.%m.%Y') > ".$values_none;

Thank you for your help.
Are you satisfied with our products/services/support?
Please help us to keep improving, add a review at  joomla extensions site and  magento connect

Our clients' feedback