>>>>>I have 3 parameters I am trying to pass to a stored procedure
>>>>>They are date fields in the parameters, not date time.
>>>>>The SP variables are string.
>>>>>
>>>>>Is this the correct syntax in Foxpro 9?
>>>>>I am not getting a cursor to be generated.
>>>>>The SP works when I enter 01/01/2009 and 02/01/2009 in QA
>>>>>
>>>>>ldStartDate={}
>>>>>ldEndDate={}
>>>>>ldStartDate=thisform.startdate.value
>>>>>ldEndDate=thisform.enddate.value
>>>>>
>>>>> lcSQLCMD="exec get_invoices ?ldStartDate,?ldEndDate"
>>>>> SQLEXEC(gnConnect,lcSQLCMD,'jobcost')
>>>>>
>>>>>
>>>>>the parameters in the SP are this
>>>>>
>>>>>Alter PROCEDURE get_invoices
>>>>> @ldStartDate Char(12) = '',
>>>>> @ldEndDate Char(12) = ''
>>>>>AS
>>>>
>>>>Andy,
>>>>Why are you not using date or datetime in your SP to prevent ambigiuties? Anyway if you want them as strings you can have them as strings like that. First decide what does '02/01/2009' mean. Say, if it is Feb 1, 2009 and you have set date to mdy then you could construct the string in VFP like this:
>>>>
>>>>
ldStartDate=thisform.startdate.value
>>>>ldEndDate=thisform.enddate.value
>>>>
>>>>lcStartDate = dtoc(m.ldStartDate)
>>>>lcEndDate = dtoc(m.ldEndDate)
>>>>lcSQLCMD="exec get_invoices ?m.lcStartDate,?m.lcEndDate"
>>>>SQLEXEC(gnConnect,lcSQLCMD,'jobcost')
>>>>
>>>>PS: It is better to have date/datetimes as date/datetime on both sides instead of doing character conversions.
>>>>Cetin
>>>
>>>Thanks
>>>
>>>so it should be this?
>>>Alter PROCEDURE get_invoices
>>> @ldStartDate DateTime,
>>> @ldEndDate DateTime
>>>What happens if there is a time in the database but I am only getting the date as a prompt?
>>
>>I am not sure I understand. A date would be passed as a datetime parameter. Internally a date like 1/1/2000 is considered to be a datetime value of 1/1/2000 00:00:00 (or 12:00:00 AM).
>>Cetin
>
>Correct but i in the database the end date was greater than 1/1/2000 00:00:00 (or 12:00:00 AM).
>say 1/1/2000 10:00:00 it would not be in the report
>
>the query uses invdate between startdate and end date 01/01/2000 10:00:00 is greater than 1/1/2000 00:00:00
>and would not be in the results
>
>but if it was al just date it would be
Ah I see what you mean now. Then you do something like this:
...
@ldStartDate Datetime. @ldEndDate Datetime
.... where myDate >= @ldStartDate and myDate < @ldEndDate
Say you want records on 1/1/2000 and 1/2/2000 then you would pass:
ldStart = date(2000,1,1)
ldEnd = date(2000,1,2) + 1
SQLExec(m.gnHandle, "exec myProc ?m.ldStart, ?m.ldEnd","result")
If you need it for a single date then that could be done like:
... where diffdate(d, @ldDate, getdate()) = 0
Cetin