Subject Re: SQL where with date variable
From Mervyn Bick <mervyn@invalid.invalid>
Date Thu, 21 Nov 2019 14:32:40 +0200
Newsgroups dbase.getting-started

On 2019/11/21 11:21, Akshat Kapoor wrote:

> select * from day18 where ddate >"20190401"
> select * from day18 where ddate >{2017.04.01}
> select * from day18 where ddate >{01.04.2017}
>
> All of the above statements give error

Firstly, it depends on whether one is using localSQL or "proper" SQL.

In localSQL using a SELECT statement like that the date must be given as
a literal in either American  (mm/dd/yyyy) format or German (dd.mm.yyyy)
format no matter what the date format is set to on the computer.

The different flavours of "proper" SQL are generally more flexible and
will usually accept dates in any one of several different formats.

The following should both work with localSQL.

select * from day18 where ddate > '04/01/2019'
select * from day18 where ddate > '01.04.2019'


> But I do not get any error in any of the following
> In fact query with parameters are my first preference
>
> q = new query()
> q.sql = "select * from day18 where ddate >:xdate"
> q.params["xdate"] = date()
> q.active = true
> q.params["xdate"] = date()-10
> q.requery()
>

When used as a parameter for a query the date must be in date format.
In this case the date format set on the computer determines the syntax.

The date format on my computer is YMD so I would use

q.params['xdate'] = {2019-04-01}
or
q.params['xdate'] = new date(2019,3,1)

If the date format on the computer is American

q.params['xdate'] = {4/1/2019}
or
q.params['xdate'] = new date(2019,3,1)


If the date format on the computer is British

q.params['xdate'] = {1/4/2019} //the separator is / and not . as for German
or
q.params['xdate'] = new date(2019,3,1)

Mervyn.