Discussion:
SELECT using DATE
(too old to reply)
John Cosmas
2005-06-15 04:46:20 UTC
Permalink
I have a SELECT statement that picks up the date from a variable that I've
assigned its value earlier.

pstrStartDate = "2005/06/01"
pstrEndDate = "2005/06/02"
SELECT * ;
FROM car_stat ;
WHERE car_stat.a LIKE "WC2005" ;
AND car_stat.ad BETWEEN {^"+pstrStartDate+"} AND {^"+pstrEndDate+"} ;
AND car_stat.status IN ("D") ;
ORDER BY car_stat.k4 ;
INTO TABLE wrk_car_stat

However, this causes the VFP 6.0 to generate an error. Please help.
Stefan Wuebbe
2005-06-15 05:44:13 UTC
Permalink
You can transform character values to dates and back, but it's almost
always easier to stay with one type

dStartDate = Date(2005,6,1)
dEndDate = Date(2005,6,2)
SELECT * ... ;
WHERE ... ;
AND car_stat.ad BETWEEN m.dStartDate AND m.dEndDate ;


hth
-Stefan
--
|\_/| ------ ProLib - programmers liberty -----------------
(.. ) Our MVPs and MCPs make the Fox run....
- / See us at www.prolib.de or www.AFPages.de
-----------------------------------------------------------
I have a SELECT statement that picks up the date from a variable that I've assigned
its value earlier.
pstrStartDate = "2005/06/01"
pstrEndDate = "2005/06/02"
SELECT * ;
FROM car_stat ;
WHERE car_stat.a LIKE "WC2005" ;
AND car_stat.ad BETWEEN {^"+pstrStartDate+"} AND {^"+pstrEndDate+"} ;
AND car_stat.status IN ("D") ;
ORDER BY car_stat.k4 ;
INTO TABLE wrk_car_stat
However, this causes the VFP 6.0 to generate an error. Please help.
Eric den Doop
2005-06-15 05:46:01 UTC
Permalink
Try:
...
AND car_stat.ad BETWEEN ;
EVALUATE("{^"+pstrstartdate+"}") AND ;
EVALUATE("{^"+pstrenddate+"}")
...
--
Eric den Doop
www.foxite.com - The Home Of The Visual FoxPro Experts - Powered By VFP8
Post by John Cosmas
I have a SELECT statement that picks up the date from a variable that I've
assigned its value earlier.
pstrStartDate = "2005/06/01"
pstrEndDate = "2005/06/02"
SELECT * ;
FROM car_stat ;
WHERE car_stat.a LIKE "WC2005" ;
AND car_stat.ad BETWEEN {^"+pstrStartDate+"} AND {^"+pstrEndDate+"} ;
AND car_stat.status IN ("D") ;
ORDER BY car_stat.k4 ;
INTO TABLE wrk_car_stat
However, this causes the VFP 6.0 to generate an error. Please help.
Carsten Bonde
2005-06-15 05:53:24 UTC
Permalink
John,

the datatype is not correct. You need to convert:

pstrStartDate = "2005/06/01"
pstrEndDate = "2005/06/02"

SELECT * ;
FROM car_stat ;
WHERE car_stat.a LIKE "WC2005" ;
AND car_stat.ad BETWEEN ctod(pstrStartDate) AND ctod(pstrEndDate) ;
AND car_stat.status IN ("D") ;
ORDER BY car_stat.k4 ;
INTO TABLE wrk_car_stat



--
Cheers
Carsten
_______________________________
Post by John Cosmas
I have a SELECT statement that picks up the date from a variable that I've
assigned its value earlier.
pstrStartDate = "2005/06/01"
pstrEndDate = "2005/06/02"
SELECT * ;
FROM car_stat ;
WHERE car_stat.a LIKE "WC2005" ;
AND car_stat.ad BETWEEN {^"+pstrStartDate+"} AND {^"+pstrEndDate+"} ;
AND car_stat.status IN ("D") ;
ORDER BY car_stat.k4 ;
INTO TABLE wrk_car_stat
However, this causes the VFP 6.0 to generate an error. Please help.
Juan Alonso
2005-06-22 20:06:07 UTC
Permalink
John.

Lets keep it simple.

pstrStartDate = ctod('06/01/2005')
pstrEndDate = ctod('06/06/2005')

SELECT * ;
FROM car_stat ;
WHERE car_stat.a LIKE "WC2005" ;
AND car_stat.ad BETWEEN pstrStartDate AND pstrEndDate ;
AND car_stat.status IN ("D") ;
ORDER BY car_stat.k4 ;
INTO TABLE wrk_car_stat
Post by John Cosmas
John,
pstrStartDate = "2005/06/01"
pstrEndDate = "2005/06/02"
SELECT * ;
FROM car_stat ;
WHERE car_stat.a LIKE "WC2005" ;
AND car_stat.ad BETWEEN ctod(pstrStartDate) AND ctod(pstrEndDate) ;
AND car_stat.status IN ("D") ;
ORDER BY car_stat.k4 ;
INTO TABLE wrk_car_stat
--
Cheers
Carsten
_______________________________
Post by John Cosmas
I have a SELECT statement that picks up the date from a variable that I've
assigned its value earlier.
pstrStartDate = "2005/06/01"
pstrEndDate = "2005/06/02"
SELECT * ;
FROM car_stat ;
WHERE car_stat.a LIKE "WC2005" ;
AND car_stat.ad BETWEEN {^"+pstrStartDate+"} AND {^"+pstrEndDate+"} ;
AND car_stat.status IN ("D") ;
ORDER BY car_stat.k4 ;
INTO TABLE wrk_car_stat
However, this causes the VFP 6.0 to generate an error. Please help.
Juan Alonso
2005-06-23 18:09:29 UTC
Permalink
Oh I almost forgot.

You can do it this way too.

pstrStartDate = date(2005, 06, 01)
pstrEndDate = date(2005, 06, 06)

SELECT * ;
FROM car_stat ;
WHERE car_stat.a LIKE "WC2005" ;
AND car_stat.ad BETWEEN pstrStartDate AND pstrEndDate ;
AND car_stat.status IN ("D") ;
ORDER BY car_stat.k4 ;
INTO TABLE wrk_car_stat
Post by Juan Alonso
John.
Lets keep it simple.
pstrStartDate = ctod('06/01/2005')
pstrEndDate = ctod('06/06/2005')
SELECT * ;
FROM car_stat ;
WHERE car_stat.a LIKE "WC2005" ;
AND car_stat.ad BETWEEN pstrStartDate AND pstrEndDate ;
AND car_stat.status IN ("D") ;
ORDER BY car_stat.k4 ;
INTO TABLE wrk_car_stat
Post by John Cosmas
John,
pstrStartDate = "2005/06/01"
pstrEndDate = "2005/06/02"
SELECT * ;
FROM car_stat ;
WHERE car_stat.a LIKE "WC2005" ;
AND car_stat.ad BETWEEN ctod(pstrStartDate) AND ctod(pstrEndDate) ;
AND car_stat.status IN ("D") ;
ORDER BY car_stat.k4 ;
INTO TABLE wrk_car_stat
--
Cheers
Carsten
_______________________________
Post by John Cosmas
I have a SELECT statement that picks up the date from a variable that I've
assigned its value earlier.
pstrStartDate = "2005/06/01"
pstrEndDate = "2005/06/02"
SELECT * ;
FROM car_stat ;
WHERE car_stat.a LIKE "WC2005" ;
AND car_stat.ad BETWEEN {^"+pstrStartDate+"} AND {^"+pstrEndDate+"} ;
AND car_stat.status IN ("D") ;
ORDER BY car_stat.k4 ;
INTO TABLE wrk_car_stat
However, this causes the VFP 6.0 to generate an error. Please help.
Olaf Doschke
2005-06-24 07:10:00 UTC
Permalink
Post by John Cosmas
pstrStartDate = "2005/06/01"
pstrEndDate = "2005/06/02"
If these two vars originally come
from a form from two Textbox.Values,
then make these Textboxes Date input
boxes by initially setting their Values
to {}, the empty date.

The date format foxpro that really is universal is
{^yyyy-mm-dd} or {^yyyy-mm-dd hh:mm:ss},
not {^yyyy/mm/dd}, although that should also work.

But it must be

{^&pstrStartDate} AND {^&pstrEndDate}
not
{^"+pstrStartDate+"} AND {^"+pstrEndDate+"} ;

& will expand the content of a variable, which
is called makro substitution, a hell of a feature
other languages lack.

If makro substitution is done {^&pstrStartDate}
will evaluate to {^2005/06/01}.

Bye, Olaf.

Loading...