Subject Re: date data in SQL statement From Pieter van Heerden <psvh@mweb.co.za> Date Fri, 24 Apr 2015 22:46:59 -0400 Newsgroups dbase.getting-started Mervyn Bick Wrote: > On Fri, 24 Apr 2015 16:05:40 +0200, Pieter van Heerden <psvh@mweb.co.za>   > wrote: > > > In teh follwing: > > > > q.sql = "select * where rdate >= :startdat and rdate < :enddate" > > q.params["startdate"] = form.startdate.value > > q.params["enddate"] = form.enddate.value > > > > the startdate and enddates seems to be ignored, e.g. extraction will   > > take place to beyond enddate.  Form.startdate and form.enddate comes   > > from entryfields on the form linked to date spinbox fields in the table   > > that is formatted as DMY. > > > > I have tried to recreate the SQL query in the SQL builder by inputting   > > specific dates, but is then confronted with the problem  that date   > > format seems to be wrong - error "Type mismatch in expression".  How   > > does one enter date in the SQL builder, I have tried '...', {...},   > > ctod(), date(), {d '...'} but none works as defined in SQL manuals I   > > have looked at and I could not find the correct format explained in the   > > OLH system.  The date format in this case is assumed to be yyyy-mm-dd. > > > > Where woiuld I find an example code in dBase help function that would   > > show me what a date format in an SQL statement should look like?  The   > > date() function under local SQL tells me about extract() but not about   > > what the date should look like. > > Try the following > > q.sql = "select * where rdate between :startdate and :enddate" > q.params["startdate"] = form.startdate.value > q.params["enddate"] = form.enddate.value > > The BETWEEN predicate will include the start date and the end date in the   > selection.  If you don't want the end date included in the selection as   > indicated by your use of rdate < :enddate then simply deduct one from the   > value supplied by form.enddate.value. > > q.params["enddate"] = form.enddate.value - 1 > > > In a localSQL statement there are only two formats in which to enter a   > literal date namely 'mm/dd/yyyy' (American) or 'dd.mm.yyyy' (German).   > This applies no matter what the computer's DATE format is set to.  This   > information is tucked away in the localSQL help file at C:\Program   > Files\Common Files\Borland\BDE\localsql.hlp   Have a look at "date   > formats" in this file. > > Have a look at the code at the top of the attached example.  I've used the   > American format to create the table even though the date format on my   > computer is YMD. > > When supplying parameters to a query the values must be in the computer's   > DATE format.  In the attached example program I've used > >        params["startdate"] = dttod(new date(1900,0,1)) >        params["enddate"] = dttod(new date(2020,11,31)) > > I've done this so that the example will work no matter what your date   > format is set to.  If you open the form in the designer dBASE will stream   > out a dBASE literal date using curly brackets in the format set on the   > computer. > > Mervyn. Thanks, now for the next question relating to this: If the enddate value is a later date than the date of the last entry in the queried table, the field values of the last record is repeated/appended to the result rowset until the enddate value is reached.  Surely extraction should stop when the last record is reached even though enddate is still somewhere in the future?