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

try this:

create a table and a stored proc:

IF OBJECT_ID('foo') IS NOT NULL DROP TABLE foo
GO
CREATE TABLE foo (
        ID              INT         IDENTITY(1,1)
        , StartDate     DATETIME    NOT NULL
        )
GO
IF OBJECT_ID('fooproc') IS NOT NULL DROP PROC fooproc
GO

AS
        -- warning! using implicit conversion
        INSERT foo(StartDate)

GO

check this VB code:

    Dim dStartDate      As Date
    Dim lMiliseconds    As Long

    With New Command
        .CommandType = adCmdStoredProc
        .CommandText = "fooproc"
        .Parameters.Append .CreateParameter(, adVarChar, , 50, "2003-05-10
17:55:28.855")
        .ActiveConnection = CONN_STR
        .Execute
    End With
    With New Recordset
        .CursorLocation = adUseClient
        .Open "SELECT * FROM foo ORDER BY ID DESC", CONN_STR
        '--- extract datetime and miliseconds separately
        dStartDate = CDate(CStr(!StartDate))
        lMiliseconds = (CDbl(!StartDate) - CDbl(dStartDate)) * (1000# * 60 *
60 * 24)
        If lMiliseconds < 0 Then
            dStartDate = DateAdd("s", -1, dStartDate)
            lMiliseconds = lMiliseconds + 1000
        End If
        Debug.Print !StartDate
        Debug.Print dStartDate & "." & Format(lMiliseconds, "000")
    End With

your orginal problem stems from OLE datetime conversion routines. to
circumvent them you can pass strings (VARCHARs) and do the conversion inside
the stored proc. either use ODBC cannonical datetime format ot ISO one
(SELECT CONVERT(DATETIME, '20030511 18:01:42.35'))

a second problem you are facing is the SQL Server DATETIME columns have a
precision of 1/3 of a 1000th of a second so you always get 0, 3 or 7 for the
3rd digit after seconds (38.250 seconds, 38.253 seconds and 38.257 seconds).

HTH,
</wqw>


Quote:
> 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:

> 121))


> 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.



Thu, 27 Oct 2005 23:00:26 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