Date returns date and previous day for some reason

Classic Servoy
1

Hi guys,

I’m running into some strange behaviour and do not understand why this happens.
It would appear to be a bug.

I have a form in which you can search for contracts that started on a certain selected date.

The method sets the startdate search variable in this way:
if(globals.immocontract_search_start)
{
var thestart = (globals.immocontract_search_start.getFullYear() * 10000) + (( globals.immocontract_search_start.getMonth() + 1) * 100) + globals.immocontract_search_start.getDate();
}

Do the same to select the second date to enable search between date ranges and then create the following string:

if(thestartu)
{
startdate_string = thestart + “…” + thestartu
}
if(!thestartu && thestart)
{
startdate_string = thestart
}

calculation contains the following:
function startdate_string()
{
if(startdate)
{
return ( startdate.getFullYear() * 10000) + (( startdate.getMonth() + 1) * 100) + startdate.getDate();
}
}

When I do a search i get returned not only the values for the selected date but also the records for the previous date.

Thanks in advance for your advice.

Regards,
John

2

John,

What is the actual startdate_string used in the search?
The … translates to between in sql and is inclusive.

Try the query
select * from where startdate_string between and

Does this give the expected results?

Note that in Servoy 5 (currently in RC), has improved support for date searches, you can use the # in combination with the … on dates directly now.

startdate = ‘#01/05/2002…05/05/2002|dd/MM/yyyy’ // will search from start of may 1 to end of may 5

Rob

3

Hi,

I think I have gone too deep before with the ranges.

I would like to get back to the basics.

If we do not fill in the second values for the range we will just put the first startdate field on for example September 1st.

We do the same for the enddate field, both are date formatted in the database.

When I do a search via the controller.find() /fill in all the required fields/ controller.search() I get returend values for August 31st.

This takes away the range issue, which I am keeping in mind but shows once again the issue with the date searches.

When performing some test I can conclude:

  1. select * from immocontract where startdate = (‘2009/09/01’) and stopdate = (‘2009/09/01’) and office = 3;
    returns nothing, most likely because the hour is not added in the search?
    The dataProvider format for the fields is CALENDAR and seems to contain the hour as well.
    The deducted values are already stated above and are declared as var.

  2. When I use above deducted values and run this exact sql query via Sybase Central
    select * from immocontract where startdate = (‘2009/09/01 16:30’) and stopdate = (‘2009/09/01 16:30’) and office=3;
    I get returned nothing.

  3. select * from immocontract where startdate > (‘2009/08/31’) and stopdate < (‘2009/09/02’) and office = 3;
    returns the 3 wrong values from August 31st. Technically not wrong since as stated above the searches are inclusive for the dates.

  4. select * from immocontract where startdate = (‘2009/08/31 16:30’) and stopdate = (‘2009/08/31 16:30’) and office=3;
    also returns the values I actually get returned in my form.

I am not aware of the core code servoy uses to run it’s search when you have filled in a specific date.

As it also return hits from the day itself it is certainly not #4 which gets executed.

I have the impression it is more something like #3 but I can not verify this.

Could someone shed some light on what servoy actually does during it’s controller.search() for the date fields?

Many thanks in advance for your feedback.

John

4

I have just run into another form where we can request invoices for a specific year and month.

16.30 seems to be the magic number.

When I request invoices for 2009/08 I get returned all the values from 31/7 16:30 to 31/8 16:29.

Seems like the server is using a different timezone than my client in some way?

I ran a select on the db though and this gives:

getdate(*)
‘2009-10-29 21:41:54.984’

which is exactly my date.

We have seen in the previous post that he is holding 16.30 as a value for our testcase but still he does not return this during the search.

I am a bit confused…

5

Perhaps a search time zone forum messages with server might help …