Subject Re: date data in SQL statement From Pieter van Heerden <psvh@mweb.co.za> Date Fri, 24 Apr 2015 23:34:12 -0400 Newsgroups dbase.getting-started Pieter van Heerden Wrote: > 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? > Oops, sorry, the root cause of my problem seem to be contained in the sql statement: qp.sql = "select cropid, Growday, Growday growday_tab, week, et0, ke, kc kc_tab, kcb kcb_tab, kcmax, etc etc_tab, etc etc_cum_tab, growstage stage, rain, cropcover, growday groupday, rdate from irricrop3 where cropid = :cropid" When I replaced all the column names and replaced the whole lot with: qp.sql = "select * from irricrop3 where cropid = :cropid" the problem disappeared. Any reason why?