HELP! Needed with Fox Pro date math

[Dave Navarro]

I am accessing a Fox Pro database through ODBC in ASP.

I have a table with two dates in it. A start date and an end date. I
need to create a table with the difference between the two dates.

Is the only way to load each record into an array and calculate the
difference myself, or can it be done in the SELECT statement?

--Dave

[Aaron Bertrand [MVP]]

Does FoxPro not have a DATEDIFF statement?

INSERT INTO newTable(primary_key, Diff)
SELECT primary_key, DATEDIFF(?, DateCol1, DateCol2)
FROM oldTable

I put ? in there for two reasons: (a) I don't know if FoxPro accepts string
parameters or named constants, and (b) you didn't bother telling us what
difference you want (hours, days, years, ...)



"Dave Navarro" <dave@dave.dave> wrote in message
news:MPG.199fce26bd82f77f989746@news-east.giganews.com...

> I am accessing a Fox Pro database through ODBC in ASP.
>
> I have a table with two dates in it. A start date and an end date. I
> need to create a table with the difference between the two dates.
>
> Is the only way to load each record into an array and calculate the
> difference myself, or can it be done in the SELECT statement?
>
> --Dave
>

[Dave Navarro]

In article <uR9Ayu0XDHA.1384@TK2MSFTNGP10.phx.gbl>,
[email]aaron@TRASHaspfaq.com[/email] says...

> Does FoxPro not have a DATEDIFF statement?
>
> INSERT INTO newTable(primary_key, Diff)
> SELECT primary_key, DATEDIFF(?, DateCol1, DateCol2)
> FROM oldTable
>
> I put ? in there for two reasons: (a) I don't know if FoxPro accepts string
> parameters or named constants, and (b) you didn't bother telling us what
> difference you want (hours, days, years, ...)

Apparently Fox Pro does not.

I need the number of days between two dates. It's a real estate
application and I need to display a table showing the number of days
between when a house was listed on the market and when it sold, sorted
by the number of days it took to sell.

The only way I can think of is to loop through the entire database and
build and array. Very slow, but it works.

--Dave

[Bob Barrows]

Dave Navarro wrote:

> In article <uR9Ayu0XDHA.1384@TK2MSFTNGP10.phx.gbl>,
> [email]aaron@TRASHaspfaq.com[/email] says...

>> Does FoxPro not have a DATEDIFF statement?
>>
>> INSERT INTO newTable(primary_key, Diff)
>> SELECT primary_key, DATEDIFF(?, DateCol1, DateCol2)
>> FROM oldTable
>>
>> I put ? in there for two reasons: (a) I don't know if FoxPro accepts
>> string parameters or named constants, and (b) you didn't bother
>> telling us what difference you want (hours, days, years, ...)

>
> Apparently Fox Pro does not.
>
> I need the number of days between two dates. It's a real estate
> application and I need to display a table showing the number of days
> between when a house was listed on the market and when it sold, sorted
> by the number of days it took to sell.
>
> The only way I can think of is to loop through the entire database and
> build and array. Very slow, but it works.
>
> --Dave

1. Don't loop through your "database". If you're going to create an array,
use GetRows.
2. You will have a better chance of discovering the FOXPRO syntax by asking
on a FOXPRO newsgroup. Just because you're using ASP doesn't mean you HAVE
to ask your question on an ASP newsgroup. Your question has to do with a
FOXPRO query, which is totally separate from ASP (remember, you could just
as well be running the query within the FOXPRO environment). Here's a likely
candidate:
microsoft.public.fox.vfp.queries-sql

HTH,
Bob Barrows