DateTime problem in query

Classic Servoy
1

Hello,

I have this code:

var start = new Date();
var end = new Date(start.getFullYear(), start.getMonth(), start.getDate(), 23, 59, 59);
var maxReturnedRows = 100;
var query = 'SELECT id FROM table where date_field >= ? AND date_field <= ?';
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, new Array(start, end), maxReturnedRows);

Servoy generates this query:

used sql SELECT id FROM table WHERE date_field >= ? AND date_field <= ?
questiondata[0]= 2005-03-13 ,type: java.sql.Date
questiondata[1]= 2005-03-13 ,type: java.sql.Date

This query doesn’t care about the time portion of the query ?! Instead of “2005-03-13” I’d expect “2005-03-13 13:41:22” and “2005-03-13 23:59:59” :?

Why is this? The column is a datetime in MS SQL Server 2000.

Any ideas?

2

Can you check the query it generates in the admin pages?
I understand you use Servoy2.2 so you can see what Servoy fires at the database.

3

This IS what Servoy uses (from the stack trace):

used sql SELECT id FROM table WHERE date_field >= ? AND date_field <= ?
questiondata[0]= 2005-03-13 ,type: java.sql.Date
questiondata[1]= 2005-03-13 ,type: java.sql.Date

That’s what I wrote about :)

4

ACK! :shock:
You’re right.
I just saw that the database performance page shows these kinds of queries. Not much help there.

What happens when you format the query yourself? Does that work?
Else try using variables of the type String.

5

Yes, of course, I can manually create a date string. The problem with this is that some databases like it this way, some in another way. I’d prefer to let Servoy do it. The question is: why does it use a java.sql.Date without time portion?

6

Could someone help me with this? If I manually construct the query like

var today = new Date()
var queryDate = today.getFullYear() + '-' + utils.stringRight('0' + (today.getMonth()+1), 2) + '-' + utils.stringRight('0' + today.getDate(), 2) + ' ' + today.getHours() + ':' + utils.stringRight('0' + today.getMinutes(), 2) + ':00'

and then use queryDate in the SQL-query I am getting the right results on an US English MS SQL Server. If I use the same query on a German SQL Server it doesn’t work (I am getting some arithmetic overflow error). Somehow this is understandable since the German date format differs from the US date format. Of course I could use a formatter with my query but that looks different on every database.

I was hoping if Servoy does the job the date gets formatted in the right way (and I think it is). But this is useless if I time portion is passed to the database if I want to.

Am I doing something wrong or is Servoy or is Microsoft?

Thanks
Patrick

7

Is this issue, solved allready?

I have the following code:

var startDate = new Date(2006, 6, 10, 0, 0, 0)
var endDate = new Date(2006, 6, 16, 23, 59, 59)
var query = "SELECT activitiesid, startdatetime, stopdatetime FROM activities "
query += "WHERE startdatetime >= ?"
query += " AND stopdatetime <= ?"
application.output(query)

var args = new Array();
args[0] = startDate
args[1] = endDate
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, args, -1);
application.output(dataset.getAsHTML())

All activities on 16-07-2006 are NOT found in this querie!
It looks like the time-portion is not handled by Servoy.

I have filled in a report in the Servoy Support solution.

8

What servoy version are you using?
And if you have tracing on. What kind of sql and sql parameters are sent to the server?

9

version 2.2.5 and sybase 9.01

did not use the trace for that, have to check that first.

10

ok somehow that fix was not in 2.2.5 but it is in the current code base.
It will be in 2.2.6