The DTOC() fixed the problem


Here's the foxpro code


ldStartDate={}
ldEndDate={}
ldStartDate=thisform.startdate.value
ldEndDate=thisform.enddate.value
ldEndDate=dtoc(ldEndDate)

SQLEXEC(gnConnect,lcSQLCMD,'jobcost')

* To avoid a busy connection error this must be done
* after SQLEXEC() is on complete
IF SQLGetProp( gnConnect , 'ConnectBusy')
SQLCANCEL(gnConnect )
ENDIF

SELECT jobcost
GOTO TOP
SCAN WHILE !EOF()
lnInvno=0
lnInvno=jobcost.invno

lcSQLCMD=[SELECT ISNULL(SUM(taxamt),0) as tax FROM arjobtax WHERE arjobtax.invno=?lnInvno]

SQLEXEC(gnConnect,lcSQLCMD,'tax')
* To avoid a busy connection error this must be done
* after SQLEXEC() is on complete
IF SQLGetProp( gnConnect , 'ConnectBusy')
SQLCANCEL(gnConnect )
ENDIF

SELECT Tax
REPLACE jobcost.tax with tax.tax

ENDSCAN
IF USED("tax")
SELECT tax
USE
ENDIF

SELECT jobcost
GOTO TOP
SCAN WHILE !EOF()

@ 2,0 SAY "Gathering costs. Please wait..."

etc..



here's the SP


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*Create PROCEDURE get_invoices */
Alter PROCEDURE get_invoices
@ldStartDate DateTime,
@ldEndDate DateTime
AS

SELECT arjobhd.custno,
arjobhd.customer,
ISNULL(ssslsman.salespn,'') as salespn,
arjobhd.transtype,
arjobtax.taxcode,
IsNull(arjobhd.invno,0) as invno,
arjob.ljob,
bbjthead.id,
ISNULL(SUM(arjob.price),0) as invoice,
ISNULL(SUM(arjob.frtamt),0) as freight,
CAST(00.00 AS decimal(10,2)) as tax,
CAST(00.00 AS decimal(10,2)) as cost_lab,
CAST(00.00 AS decimal(10,2)) as cost_mat,
CAST(00.00 AS decimal(10,2)) as cost_fin,
CAST(00.00 AS decimal(10,2)) as cost_pur,
CAST(00.00 AS decimal(10,2)) as Totcost,
CAST(00.00 AS decimal(10,2)) as TotInv,
CAST(00.00 AS decimal(10,2)) as profper
FROM arjobhd
LEFT JOIN arjob on arjobhd.invno=arjob.invno
LEFT JOIN arjobtax on arjobhd.invno=arjobtax.invno
LEFT JOIN bbjthead on bbjthead.ljob=arjob.ljob
LEFT JOIN ssslsman on arjobhd.salespnno=ssslsman.salespnno
WHERE
( bbjthead.closedate between @ldstartdate and @ldenddate
AND arjobhd.invdate IS NOT NULL
AND bbjthead.closedate >= arjobhd.invdate )
OR
( arjobhd.invdate between @ldstartdate and @ldenddate
AND bbjthead.closedate IS NOT NULL
AND invdate >= bbjthead.closedate )
Group by arjobhd.custno,
arjobhd.customer,
ssslsman.salespn,
arjobhd.transtype,
arjobtax.taxcode,
arjobhd.invno,
arjob.ljob,
bbjthead.id
ORDER by arjob.ljob, arjobhd.invno


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

>1. What do you store in the variables in the form
>2. What is the format of the fields in a table (date or datetime)
>
>3. What do you want your SP to do (you can post the whole code).
>
>>if either start or end date is empty the program aborts
>>I was concerned about a time in the datetime data type in SQL
>>I prompt to 02/02/2010 and 02/02/2010:11:00 it would be greater than 02/02/2010
>>
>>If there is a time and I only prompt for a date.
>>
>>The database I am selecting from I have no control over.
>>
>>
>>>What is the reason for using character strings for dates in your SP? If you want (for some reason) to use characters, then you need to pass them as characters from VFP. You can use DTOS() function for this.
>>>
>>>Also, what empty string will mean?
>>>
>>>>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