Count days between two dates

[Shout]

Please help:

Problem: Sort accounts into how many days between repodate and saledate.

Table Name: repotable
Fields: acctno, repodate, saledate, soldamt, saledate, soldyrmo
numdays = saledate - repodate

Example of my SQL (but it doesn't work in Fox Pro)

Select acctno, repodate, saledate, soldamt, saledate - repodate as numdays from repotable where soldyrmo = 200301 order numdays desc

The error must be the "saledate - repodate as numdays"

Result should be be:
acctno - repodate - saledate - soldamt - numdays

--- field explanation --
acctno = account number
repodate = Repossession date of item
saledate = sale date of repo item
soldyrmo = Sold in terms of year and month
numdays = number of days between repodate and saledate
----------------------------------------------------------------------------
Thanks for any help.

FoxPro 3 version user.

 

[edrest]

Try.....

(saledate - repodate) as numdays

putting the ( ) should solve the problem.


Tom Gahagan
edrest@alltel.net

REST



If you get a chance to sit out or dance...

I hope you dance. L Wommack

 

[Shout]

Thanks for your reply, but I have tried that too.. It doesn't work. Thanks.

 

[mm0000]

Shouldnt the SQL statment end with ' order by numdays desc' rather than ' order numdays desc'

HTH

 

[rob444]

Note that when calculating with dates in SQL the most actual date should come first:
(mostactualdate - lessactualdate)
If you're not always sure on that then put ABS() around it.
I agree that putting ( ) around it will probably solve the problem - AND using the BY statement after ORDER.

Rob.

 

[Mike Gagnon]

Shout

Problem: Sort accounts into how many days between repodate and saledate.

Thanks for your reply, but I have tried that too.. It doesn't work. Thanks.

The problem is you are not explaining anywhere in your two posts what is the problem. It doesn't work just doesn't help.[/b] Could you try to be more clear as to what is the problem. I have recreated your sample with my own data and it works for me:

Select acctno, repodate, saledate, soldamt, saledate - repodate as numdays from repotable where soldyrmo = 200301 order numdays desc

Typically should read :
Select acctno, repodate, saledate, soldamt, (saledate - repodate) as numdays from repotable where soldyrmo = "200301" order by numdays desc into cursor myCursor.

Providing your date fields are actual dates and your yearmonth is characters. Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[ramani]

Hi Shout

Use this code..

SELECT acctno, repodate, saledate, soldamt, ;
saledate - repodate as numdays ;
FROM repotable WHERE soldyrmo = 200301 ;
ORDER BY 5 DESC



ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com