Subject |
Re: date data in SQL statement |
From |
Mervyn Bick <invalid@invalid.invalid> |
Date |
Fri, 24 Apr 2015 18:01:02 +0200 |
Newsgroups |
dbase.getting-started |
Attachment(s) |
Unnamed File 1, Unnamed File 2, Unnamed File 3, Unnamed File 4, test_dates.wfm,
Unnamed File 6, Unnamed File 7, Unnamed File 8 |
|
| 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.
|
|
|
|
|
|
|