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.