Problem passing date to a stored procedure 1

[casperovjp]

I have a problem passing a date parameter created using CreateODBCDate to a
stored procedure here is the code
<cfset yourDate = CreateODBCDate(&quot;#URL.month#/#URL.day#/#url.year#&quot>
<cfstoredproc datasource=&quot;workflowdblocal&quot; procedure=&quot;sp_retrieve_sked&quot;>
<cfprocresult name=&quot;sked&quot; resultset=&quot;1&quot;>
<cfprocparam type=&quot;In&quot;
cfsqltype=&quot;CF_SQL_INTEGER&quot;
dbvarname=&quot;provider_id&quot;
value=&quot;1&quot; null=&quot;No&quot;>
<cfprocparam type=&quot;In&quot;
cfsqltype=&quot;CF_SQL_DATE&quot;
dbvarname=&quot;seldate&quot;
value=&quot;#yourdate#&quot; null=&quot;No&quot;>
</cfstoredproc>

 

[jalbao]

What is the error message that you are receiving?

And, just out of curiosity, see if using the CreateODBCDateTIME() function solves the problem.

 

[casperovjp]

[Microsoft][ODBC SQL Server Driver]Optional feature not implemented

This is the error that I am getting. CreateODBCDateTime doesn't work either.:-(

 

[r937]

can you see the code of the stored proc? what format does it expect?

maybe it's expecting a date string like '2003-05-14' and not an ODBC date like {d '2003-05-14'} or whatever the format is

so instead of CF_SQL_DATE in the cfprocparam, it should be CF_SQL_CHAR

or maybe try CreateDate instead

rudy

 

[casperovjp]

The stored proc is expecting a smalldatetime datatype. I tried changing it to CF_SQL_CHAR but to no avail. Then the CreateDate still gave me a datatype conversion error.

 

[SidianConsulting]

Try CF_SQL_TIMESTAMP instead of CF_SQL_DATE.

-Tek