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

I can't get it to NOT work...

---------------------------------------------------
Create Table DateInsertTest (D datetime)
GO

AS

GO



Select * From DateInsertTest
Drop Table DateInsertTest
Drop Procedure InsertTestDate
------------------------------------------------------
The above works perfectly well in Query analizer.
The above works perfectly well as DB.Execute using ADO in VB where DB is a
connection to the database.
If I leave the proc and table in the DB as above and use a parameter object
with ADO, it works just fine.

The only difference I see is I am not attempting to convert anyting.  I am
letting SQL do an implicit conversion.. have you tried that?

-John


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.



Tue, 25 Oct 2005 02:27:12 GMT  
 Passing DATETIME parameter to stored proc
I take that back...

When I use a parameter object (ADO) I get the same results as you.  Seems to
be a bug.  Very weird.

What is even weirder is this... I put in a line into my little proc that

it is getting passed to the procedure correctly...

VERY STRANGE...



Tue, 25 Oct 2005 02:48:17 GMT  
 Passing DATETIME parameter to stored proc
And another weird thing I just noticed... If I just send a straight execute
statement with the value '15:50:01.887'

The field gets populated with '15:50:01.885'

Good luck!



Tue, 25 Oct 2005 02:51:45 GMT  
 Passing DATETIME parameter to stored proc
Though you have taken it back, I tried using Query Analyzer.
I wrote the following stored proc:
------------------------------------------------------------
CREATE PROCEDURE varcharIns
(

)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO debug(bred)


    BEGIN
        RAISERROR  20000 'variant_historyInsProc: Cannot insert because
primary key value not found in variant_history '
        ROLLBACK TRAN
        RETURN(1)
    END

    COMMIT TRAN
END
GO
------------------------------------------------------------
Table 'debug' contains a column 'bred' with DATETIME data type.
Then I tried several command sequences in Query Analyzer:
------------------------------------------------------------
set dateformat ymd



------------------------------------------------------------
set dateformat ymd



------------------------------------------------------------
set dateformat ymd



------------------------------------------------------------
None of them works. I've got the following Query Analyzer error message:
------------------------------------------------------------
Server: Msg 241, Level 16, State 1, Procedure varcharIns, Line 16
Syntax error converting datetime from character string.
------------------------------------------------------------
I've tried different forms of INSERT statement (INSERT VALUE and INSERT
SELECT) with CONVERT and CAST.
None of them works. But when use the following code, everything is OK:
------------------------------------------------------------
set dateformat ymd



------------------------------------------------------------
Except that '2003-05-09 12:15:01.321' is rounded to '2003-05-09
12:15:01.320'.
I understand why this happens. You can read about this feature (!) of SQL
server in SQL Server Book (about datetime and smalldatetime).

Another stupid feature is that it rounds DATETIME value passed as Parameter
object through ADO (Note that I use VC++). Maybe OLE DB provider rounds it
when it converts double to DATETIME ('cause variant date and time value is
stored as 8-bit double). For example, I get local system time as SYSTEMTIME
structure. Then I use SystemTimeToVariantTime function to convert it to
double. For example, I have the following date: '2003-05-09 13:44:30.730'.
Double representation is 37750.572577894. The whole part (37750) represents
date. The fraction part represents time and calculates as following:
0.572577894 = 13 / 24 + 44 / (24 * 60) + 30 / (24 * 60 * 60) + 730 / (24 *
60 * 60 * 1000)
                          ^ hour     ^ minute             ^ second
^ millisecond

SystemTimeToVariantTime returns 37750.572569444 = '2003-05-09 13:44:30.000.
As you can see it ignores milliseconds stores in SYSTEMTIME. So I need to
add 730 / (24 * 60 * 60 * 1000) to correct this. The I pass corrected value
to Parameter object and call Execute. Now another stored proc will be
called:
------------------------------------------------------------
CREATE PROCEDURE datetimeIns
(


)
AS
BEGIN
    BEGIN TRAN

    INSERT INTO debug(bred)


    BEGIN
        RAISERROR  20000 'variant_historyInsProc: Cannot insert because
primary key value not found in variant_history '
        ROLLBACK TRAN
        RETURN(1)

    END

    COMMIT TRAN

END
GO
------------------------------------------------------------
As you can see stored proc has one output parameter 'out' which can be
watched through De{*filter*} after calling Execute. For my example it will be
equal to 37750.572581019 = '2003-05-09 13:44:31.000'. As you can see input
parameter was rounded to nearest whole number 31 and in this (rounded) form
was written into the table.

So the conclusion is that we can use milliseconds only in Transact-SQL. The
only way to insert DATETIME with millisecond accuracy is to pass direct
statements with adCmdText flag and avoid using stored procs.



Quote:
> I can't get it to NOT work...

> ---------------------------------------------------
> Create Table DateInsertTest (D datetime)
> GO

> AS

> GO



> Select * From DateInsertTest
> Drop Table DateInsertTest
> Drop Procedure InsertTestDate
> ------------------------------------------------------
> The above works perfectly well in Query analizer.
> The above works perfectly well as DB.Execute using ADO in VB where DB is a
> connection to the database.
> If I leave the proc and table in the DB as above and use a parameter
object
> with ADO, it works just fine.

> The only difference I see is I am not attempting to convert anyting.  I am
> letting SQL do an implicit conversion.. have you tried that?

> -John



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

milliseconds

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

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



Tue, 25 Oct 2005 17:06:02 GMT  
 Passing DATETIME parameter to stored proc
Vladimir,

the ADO data type equivalent of a SQL Datetime is adDBTimeStamp not adDate

Pete


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.



Tue, 25 Oct 2005 18:41:09 GMT  
 Passing DATETIME parameter to stored proc
However milliseconds are rounded. I don't see any difference in using adDate
and adDBTimeStamp. They are used only to help OLE DB Provider to determine
the right data type. The real transfered data type is situated in VARIANT.
It is VT_DATE represented as double.



Quote:
> Vladimir,

> the ADO data type equivalent of a SQL Datetime is adDBTimeStamp not adDate

> Pete



Wed, 26 Oct 2005 01:49:17 GMT  
 
 [ 6 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