Subtracting days in a SQL query

Classic Servoy
1

I need a SQL query which uses an integer variable to subtract days from today’s date. Earlier I thought the following hard coding worked, but now I find it doesn’t.

var today = new Date():
SELECT crid FROM cr WHERE creation_date > (? - 20)
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, [today], 1000);
2

How about

SELECT crid FROM cr WHERE creation_date < ? AND creation_date > (? - 20)

Two things you have to find out yourself:

  1. if this interesting math [date - 20] really points you to 20 days ago (I wouldn’t expect that to work on any database)
  2. if you have to use <, > or <=, >=

Hope this helps
Patrick

3

patrick:
How about

SELECT crid FROM cr WHERE creation_date < ? AND creation_date > (? - 20)

Two things you have to find out yourself:

  1. if this interesting math [date - 20] really points you to 20 days ago (I wouldn’t expect that to work on any database)
  2. if you have to use <, > or <=, >=

Hope this helps

Not really. Although I’m currently using Sybase I’d rather keep all my calls SQL generic.

Is there generic SQL available for this problem – subtract an arbitrary number of days from the current date? I rather suspect there is, but have been unable to track it down.

The 20 days above is for testing purposes only. In the final query the number of days will be conditional. Therefore I’d rather use a variable to stipulate the number of days to be subtracted in the current instance.

Hope this makes things clearer.