# SQL query problem

**URL:** <https://forum.servoy.com/t/sql-query-problem/4582>\
**Category:** Classic Servoy\
**Created:** [August 7, 2005, 9:57pm UTC](https://forum.servoy.com/t/sql-query-problem/4582 "2005-08-07T21:57:11Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Morley](https://avatars.discourse-cdn.com/v4/letter/m/7cd45c/32.png) [@Morley](https://forum.servoy.com/u/Morley)\
**Post date:** [August 7, 2005, 9:57pm UTC](https://forum.servoy.com/t/sql-query-problem/4582/1 "2005-08-07T21:57:11Z")

</div>

I want to find all records where the creation date is within the last 20 days.

This works:

```auto
var maxReturnedRows = 1000;
var today = new Date();
var days = null;
days = 20;
var query = "SELECT crid FROM cr WHERE creation_date > (? - 20)";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(),query,[today],maxReturnedRows);
controller.loadRecords(dataset);

```

And this doesn’t:

```auto
var maxReturnedRows = 1000;
var today = new Date();
var days = null;
days = 20;
var query = "SELECT crid FROM cr WHERE creation_date > (? - ?)";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(),query,[today, days],maxReturnedRows);
controller.loadRecords(dataset);

```

The only differences between the two versions is the use of a second variable for the number of days to subtract.

> (? - 20) is replaced by (? - ?)

and

> [today] is replaced by [today,days]

I’ve used this same syntax for two and more variables in other SQL queries before without problems. What am I missing here?

---

<div class="post-metadata">

**Author:** ![Riccardino](https://avatars.discourse-cdn.com/v4/letter/r/41988e/32.png) [@Riccardino](https://forum.servoy.com/u/Riccardino)\
**Post date:** [August 8, 2005, 6:12am UTC](https://forum.servoy.com/t/sql-query-problem/4582/2 "2005-08-08T06:12:10Z")

</div>

> Morley:  
> I want to find all records where the creation date is within the last 20 days.
> 
> This works:
> 
> ```auto
> var maxReturnedRows = 1000;
> 
> ```

var today = new Date();  
var days = null;  
days = 20;  
var query = “SELECT crid FROM cr WHERE creation\_date \> (? - 20)”;  
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(),query,[today],maxReturnedRows);  
controller.loadRecords(dataset);

> ```auto
> 
> And this doesn't:
> 
> ```
> 
> var maxReturnedRows = 1000;  
> var today = new Date();  
> var days = null;  
> days = 20;  
> var query = “SELECT crid FROM cr WHERE creation\_date \> (? - ?)”;  
> var dataset = databaseManager.getDataSetByQuery(controller.getServerName(),query,[today, days],maxReturnedRows);  
> controller.loadRecords(dataset);
> 
> ```auto
> 
> The only differences between the two versions is the use of a second variable for the number of days to subtract. 
> 
> > (? - 20) is replaced by (? - ?)
> 
> and
> 
> > [today] is replaced by [today,days]
> 
> I've used this same syntax for two and more variables in other SQL queries before without problems. What am I missing here?
> 
> ```

I’m I wrong or you have to put the parameters in an array?

---

<div class="post-metadata">

**Author:** ![Morley](https://avatars.discourse-cdn.com/v4/letter/m/7cd45c/32.png) [@Morley](https://forum.servoy.com/u/Morley)\
**Post date:** [August 8, 2005, 12:29pm UTC](https://forum.servoy.com/t/sql-query-problem/4582/3 "2005-08-08T12:29:30Z")

</div>

> Riccardino:  
> I’m I wrong or you have to put the parameters in an array?

From memory, I learned this technique from Maarten. I’m successfully using it elsewhere.

---

<div class="post-metadata">

**Author:** ![patrick](https://avatars.discourse-cdn.com/v4/letter/p/b2d939/32.png) [@patrick](https://forum.servoy.com/u/patrick)\
**Post date:** [August 8, 2005, 1:56pm UTC](https://forum.servoy.com/t/sql-query-problem/4582/4 "2005-08-08T13:56:45Z")

</div>

Hello Morley,

that parameter is supposed to be an Array. So what you can write is

```auto
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, new Array(today, days), maxReturnedRows); 

```

But then I don’t quite see how

```auto
... WHERE create_date > (today - 20)

```

should give you all records where the creation date is “within the last 20 days”. Why not “within the last 20 seconds” or “within the last 20 years”?

---

<div class="post-metadata">

**Author:** ![Morley](https://avatars.discourse-cdn.com/v4/letter/m/7cd45c/32.png) [@Morley](https://forum.servoy.com/u/Morley)\
**Post date:** [August 8, 2005, 3:38pm UTC](https://forum.servoy.com/t/sql-query-problem/4582/5 "2005-08-08T15:38:55Z")

</div>

> patrick:  
> that parameter is supposed to be an Array. So what you can write is
> 
> ```auto
> var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, new Array(today, days), maxReturnedRows); 
> 
> ```

Doesn’t work. So far only hard coding the number of days to subtract works.

> patrick:  
> But then I don’t quite see how
> 
> ````auto
> ... WHERE create_date > (today - 20)
> ```should give you all records where the creation date is "within the last 20 days". Why not "within the last 20 seconds" or "within the last 20 years"?
> 
> ````

That’s what I thought until I talked with a friend (not using Servoy) who said he uses it all the time. In my tests here it does indeed work – finds only records with a creation date within the past 20 days.

I’d really like a SQL date calculation which subtracts a variable number of days from today, one that does not depend on hard coding the days to be subtracted.

---

<div class="post-metadata">

**Author:** ![patrick](https://avatars.discourse-cdn.com/v4/letter/p/b2d939/32.png) [@patrick](https://forum.servoy.com/u/patrick)\
**Post date:** [August 8, 2005, 6:16pm UTC](https://forum.servoy.com/t/sql-query-problem/4582/6 "2005-08-08T18:16:04Z")

</div>

Morley,

I just tried

```auto
var c1 = 50000;
var c2 = 5;
var c3 = 100000;

var query = "SELECT id FROM table WHERE id >= (? - ?) and id <= (? + ?) ";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(),query, new Array(c3,c1,c1,c2), 10000);
var test = dataset.getAsText('\t', '\n', '\t', 1)
application.output(dataset.getAsText('\t', '\n', '\t', 1));

```

and that gives me exactly what I expected:

```auto
id	
50000	
50002	
50003	
50004	
50005

```

So this DOES work.

As I suggested before somewhere: try your SQL against your database directly- Once you have the right statement, put that into your method and replace constants by variables.

---

<div class="post-metadata">

**Author:** ![Morley](https://avatars.discourse-cdn.com/v4/letter/m/7cd45c/32.png) [@Morley](https://forum.servoy.com/u/Morley)\
**Post date:** [August 9, 2005, 12:55pm UTC](https://forum.servoy.com/t/sql-query-problem/4582/7 "2005-08-09T12:55:56Z")

</div>

Thanks Patrick, that **is** helpful. I’ll chip away at this later this morning.