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

Hi Vladimir,

    Please consider converting all Date/Time fields to dbl.

    dblTimeStamp = cdbl(mySystemConfiguredDate)
    dblTimeStamp = now

    I just finished updating all my date/time and just date and just time
fields to double precisions fields.

    I got stung on Monday June 2, 2003 where I was formatting a date field
        TimeSheetDate = format(myDate,"mmm dd, yyyy)

    I thought the mmm dd, yyyy would format myDate to June 2, 2003 whether
using MSAccess or SQL Server
        but kept returning February 6, 2003.

    The funny thing was I had spent the previous 2 weeks keeping a very
close watch over these date functions.
    And I thought they were working properly but I realized a humerous

    Firstly date functions have always been an outstanding uneasy use for
        I've developed many apps that use the date but have never been
really comfortably sure about how to handle
        all the variations.

    And now that I think back about it the following recent discovery could
have been why:

    From the 13th to the 31st day my formatting would work properly.
    As in the date formatting could NOT confuse the 13th day with a month.
    But as soon as the date changed to the 1st (thru the 12th) my date
formatting WAS getting confused with the month.

    (Please laugh with me here, bucause I found this to be increadably
frustratingly funny. Especially if you think I've been
        having trouble with this for the last 20 years.  ;-)  )

    So now I never format a database field and only pull the value from a
system date or calendar/date control
        and manipulate with dataadd functions.

    and convert back using
        myDate = cDate(dblTImeSheetDate)

        Then use the formmating for display purposes only.

I hope this helps and has made someone laugh.


> I've wrote an application that uses ADO to insert rows into table via
> 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
> 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
> 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
> (without milliseconds) '05/08/2003 21:33:23' with CAST:

> I thought that it is default string representation of DATETIME. But it
> 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
> 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

> Best regards,
> Vladimir.

Sat, 26 Nov 2005 06:31:44 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