Passing DATETIME parameter to stored proc 
Author Message
 Passing DATETIME parameter to stored proc

I've wrote an application that uses ADO to insert rows into table via stored
proc. One of the columns has DATETIME data type. I need to write date and
time with millisecond accuracy. The stored proc contains INSERT statement.
I've tried several ways as follows:
1) I create _ParameterPtr object with adDate data type via CreateParameter
and then call Execute method of _CommandPtr object. The corresponding input
parameter of stored proc has DATETIME data type. And I get the DATETIME
value rounded to second when it is being written into database (MS SQL
SERVER).
2) I create _ParameterPtr object with adVarChar data type via
CreateParameter and then call Execute method of _CommandPtr object. The
corresponding input parameter of stored proc has VARCHAR data type. I use
CONVERT function to convert VARCHAR to DATETIME as follows:



canonical. But when I call Execute method I get the following error:
    Error code: 80040e07
    Native error: 241
    Error SQL state: 22007
    Description: Syntax error converting datetime from character string.

    INSERT INTO table(time_stamp) VALUES(CONVERT(DATETIME, '2003-05-08
21:33:23.756', 121))
But this example has no practical use. I've also tried the following format
(without milliseconds) '05/08/2003 21:33:23' with CAST:

I thought that it is default string representation of DATETIME. But it also
didn't work.
But when I tried to avoid using stored proc and to pass INSERT statement
directly using adCmdText flag and 'mm/dd/yyyy hh:mm:ss' format it worked to
my surprise:
    COleDateTime oledt = COleDateTime::GetCurrentTime();
    CString tmp;
    tmp.Format("INSERT INTO table(time_stamp) VALUES(%s)",
oledt.Format("%m/%d/%Y %H:%M:%S"));
    pCmd->CommandText = tmp;
    pCmd->Execute(NULL, NULL, adCmdText);

Could anyone help me find the right way to pass DATETIME or its string
representation to stored proc? Note that I need to write milliseconds also.

Best regards,
Vladimir.



Mon, 24 Oct 2005 23:52:41 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Passing DATETIME parameter to stored proc

2. Passing DATETIME parameter to stored proc

3. Passing DATETIME parameter to stored proc

4. Datetime Parameter to SQL Server stored proc

5. How to pass datetime parameter to SQL7 Stored Procedure using VB

6. How to pass parameter to DataEnvironment (stored proc)

7. Problem passing parameters to MSSQL Svr stored proc using API

8. Passing parameter from VB6 to a CRW report based on a Stored Proc

9. Performance of Stored Proc Parameter Passing

10. passing stored proc parameters to crystal reports

11. datetime parameter from CR7 to a stored procedure in sqlserver2000

12. DATETIME - SQL Stored Procedure - Crystal Parameters

 

 
Powered by phpBB® Forum Software