Subject Re: SQL where with date variable From Robbie Nott <robnott@mweb.co.za> Date Sun, 24 Nov 2019 11:33:49 +0200 Newsgroups dbase.getting-started Thanks Mervyn Thanks for that, had no idea that there was "dBase SQL" and "proper SQL" For a program that might run on either maybe I should just stick to my tried and trusty "Select * from StockTran" and do the filtering in my Do While loops... Many thanks Robbie On 21/11/2019 14:32, Mervyn Bick wrote: > 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. > > > > >