Troubles with a SQL-query

Classic Servoy
1

I’m having troubles with an SQL-query. I have a method like this one

var query='SELECT aktiviteitid FROM aktiviteit WHERE begintijd BETWEEN \''+utils.dateFormat(globals.calendar_datum, 'yyyy-MM-dd 00:00:00.000')+'\' AND \''+utils.dateFormat(globals.calendar_datum, 'yyyy-MM-dd 23:59:59.999')+'\'';

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 1000);
controller.loadRecords(dataset);

It generates the following SQL-query

SELECT aktiviteitid FROM aktiviteit WHERE begintijd BETWEEN '2004-04-21 00:00:00.000' AND '2004-04-21 23:59:59.999'

it also generates the following error:

com.sybase.jdbc2.jdbc.SybSQLException: ASA Error -131: Syntax error near 'BETWEEN' on line 1 ASA Error -131: Syntax error near 'BETWEEN' on line 1

Now i tried using " instead of ’ and various variations…I also used another query like

SELECT aktiviteitid FROM aktiviteit WHERE begintijd >= '2004-04-21 00:00:00.000' AND begintijd <= '2004-04-21 23:59:59.999'

it generated a similar error…only instead the error beeing near to BETWEEN it now was near >=

The date-notation is according to ASA.

What am i doing wrong here?? Thx for the help in advance

Servoy Developer
Version R2 2.0 rc12-build 271
Java version 1.4.2_04-b05 (Windows 2000)

2

why aren’t you doing this:

var array = new Array(globals.calendar_datum,globals.calendar_datum);
var query='SELECT aktiviteitid FROM aktiviteit WHERE begintijd BETWEEN ? AND ?'; 

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, array, 1000); 
controller.loadRecords(dataset);

then you don’t have to worry about date conversion.
(it is from the top of my head so i don’t know it will work completel right without some modification.)

3

Let me explain what I’m trying to do…
Coz i have the feeling you dont understand me…or I dont understand you (the latter is true anyway :? )

What I want to do is:
-find all activities of a certain date (filled in globals.calendar_datum) regardless of the time
-globals.calendar_datum is filled by a calendar I made

The problem is that globals.calendar_datum has times filled in…and begintijd also…and i want to neutralize that… and i dont want to use a # search (becoz this is a slow search, i’ve been told).

First of all…I dont understand what you are doing with the array?
Second…what is wrong with the query i had (copyright it2be btw)
Third…i also tried using a relationship with for example:

globals.date1=21-04-2004 00:00:00.0
globals.date2=21-04-2004 23:59:59.999
relatie
globals.date1<=begintijd
globals.date2>=begintijd

And this relation returned no records…instead of returning the records with 21-04-2004

4

yes i needed to fix the 2 dates first.

It is much much better to use an array where you set in youre params.. Because then preparedstatements are used which are much faster, especially if you use them more then onces..

a find with # will generate that between query for you.. so it is not slower..

that relation should work. Do you have an example solution with some example data for me?