FoxPro database connector - SELECT using DATE

Author

SELECT using DATE

John Cosmas

2005-06-15, 3:25 am

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, 3:25 am

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

"John Cosmas" <johncosmas@charter.net> schrieb im Newsbeitrag
news:yoOre.4713$eL5.88@fe04.lga...
>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, 3:25 am

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

"John Cosmas" <johncosmas@charter.net> wrote in message
news:yoOre.4713$eL5.88@fe04.lga...
>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, 3:25 am

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
____________________
___________

"John Cosmas" <johncosmas@charter.net> schrieb im Newsbeitrag
news:yoOre.4713$eL5.88@fe04.lga...
> 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, 8:25 pm

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

"Carsten Bonde" <bonde AT real-inkasso DOT de> wrote in message
news:OX9AN6WcFHA.2420@TK2MSFTNGP15.phx.gbl...
> 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
> ____________________
___________
>
> "John Cosmas" <johncosmas@charter.net> schrieb im Newsbeitrag
> news:yoOre.4713$eL5.88@fe04.lga...
>
>

Juan Alonso

2005-06-23, 1:24 pm

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

"Juan Alonso" <jacinc2@hotmail.com> wrote in message
news:%23ehAVZ2dFHA.1136@TK2MSFTNGP12.phx.gbl...
> 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
>
>
> "Carsten Bonde" <bonde AT real-inkasso DOT de> wrote in message
> news:OX9AN6WcFHA.2420@TK2MSFTNGP15.phx.gbl...
>
>

Olaf Doschke

2005-06-24, 3:24 am

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

Sponsored Links