| 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? | |