date comparisons

Classic Servoy
1

I want to use a calculation to mark if a membership is active or not. Among others I have the two fields “valid_from” and “valid_to”. If valid_from is smaller than or equal to today and valid_to is bigger than or equal to today then the membership is activ.
Valid_from and valid_to are both defined as date columns in MySQL. Now the time of “application.getTimeStamp” gets in the way, when using the comparison, e.g. when valid_to equals today’s date.
I know that I can get rid of the time when searching by using “#”, but how do I do that I my case? I tried with date formating (utils.dateFormat) and JS (getDate, getMonth,…), but nothing seems to work…

Thanks a lot for input!

Reto

var l_curr_date = application.getTimeStamp();

if ( close_date && close_date < l_curr_date)
{
return null;
}
else if ( valid_to < l_curr_date )
{
return null;
}
else if ( valid_from > l_curr_date )
{
return null;
}
else if ( cNrAbo == ‘+’ && entrances_left < 1 )
{
return null;
}
else
{
return ‘+’;
};

2

Hi Retor,

Have you tried :

var l_curr_date = application.getTimeStamp(); 

if ( l_curr_date >= valid_from && l_curr_date <= valid_to)
{
	return 1;
}

Harry

3

Sorry ‘Reto’,

spelt your name incorrectly

Harry !!

4

Hi Harry

Thanks for your feedback. There’s different approches concerning the logic of setting the flag. I chose setting the ‘+’ case as “else” because I can easily add other “not valid”-criteria step by step without using nested ifs.
However, this is not my problem here - I’ll make an example:
valid_to = 9/12/2004
current_date = 9/12/2004 17:00:55
→ current_date > valid_to → active = null instead of ‘+’

I could add 23:59:59 to the valid_to date, but that it would be more elegant to trim the current date (in Oracle it would simply be trunc(sysdate))…

Reto

5

why don’t you add one day to the valid_to and then do a < (So not including the day itself (until))

ofcourse this adding of one day doesn’t have to be in the database but only in the script

6

Thanks Johan (and sorry for being so stupid…)!

This way it works fine. It even was one of my first thoughts - I just don’t know why I thought it wouldn’t work! I guess I just didn’t consider that e.g. my valid_to has no time component at all…
Anyways, it’s OK now :)

Reto