List Manager Forum

Filter View for future dates

User moon_1581 2014-04-16 01:42:03


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.


Moonsoft support 2014-04-16 10:24:20

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

User moon_1581 2014-04-24 00:14:54


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?

Moonsoft support 2014-04-24 09:55:18


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,

User moon_1581 2014-04-29 00:16:09


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.


Perfect for small code changes or to correct any bug at your site

Buy now!

Create dynamic calculators

Buy now!