Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Posts
    3

    Arrow Unanswered: FoxPro: having trouble querying on date

    I'm having similar issues as the one in this thread http://www.dbforums.com/t963737.html
    but the solution isn't working for me.

    I want to execute the following query:

    select * from payment where paymentdate = date(1999,24,11)
    or
    select * from payment where paymentdate = date(1999,24,11);

    When I open the payment table in FoxPro and execute the above query by clicking Window > Command Window and typing it into the window, I get the following error:

    Function argument value, type or count is invalid

    The previous thread says to structure the query like the one below, but I can't get it to span multiple lines and wind up getting a different error (see below).

    select *; from cashtrac; where paymntdate = date(1999,24,11);

    I get this error message:

    Command contains unrecognized phrase/keyword.


    Can someone please enlighten me as to what I'm doing wrong? I've been trying to search for correct syntax in FoxPro for over 4 hours now and trying different combinations with no luck. I normally use SQL, but have to work on this client's old DB.

    I will later have to update a different field based on a range of paymentdates.

    Thanks for your help.
    Last edited by alberto_mg; 02-03-05 at 16:11. Reason: bold format

  2. #2
    Join Date
    Aug 2004
    Posts
    28
    Ok... easy fix...


    Change the following :
    date(1999,24,11)
    TO
    date(1999, 11, 24)

    You have your Month and Days backwards !



    BTW... I would store this Date Value to a variable and then use that in your SQL statment. The way you have it FoxPro will have to evaluate that expression for each record where if it were in a variable it would do it once... when you assign the variable.

    AND..Depending on how many records you have to query it could greatly impact your speed.

    For Example do this:

    Date2Find = Date(1999, 11, 24)
    SELECT * ;
    FROM cashtrac ;
    WHERE PaymntDate = Date2Find ;
    INTO Cursor MyResultSet

    Hope this helps !
    Good Luck !

    AmcAmx

  3. #3
    Join Date
    Nov 2004
    Posts
    39
    For simplicity, you can also use {mm/dd/yyyy} date format in FoxPro.
    FoxPro Reads the data enclosed in Curly Bracket as DATE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •