Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Location
    Australia
    Posts
    217

    Question Unanswered: Stored Procedure - Passing Date Parameters Failed

    Dear all,

    I am working on an Excel VBA report which is linked to an SQL Server
    database. The front end is Excel VBA routine, the backend is SQL
    Server stored procedure.

    The VBA routine passes 2 dates to the stored procedure
    but it seems that it doesn't accept them.

    The 2 date parameters in the stored procedure are @OrderDateRangeStart
    and @OrderDateRangeEnd.

    Here is a portion of the stored procedure:

    alter proc uspSalesCommission
    ...
    ...
    @IncludeOrderDateAsCriterion int,
    @OrderDateRangeStart date,
    @OrderDateRangeEnd date
    as
    BEGIN
    ...
    ...
    WHERE (@IncludeSalesPersonsAsCriterion=0 or Staff.name in (@Salespersons)) and
    (@IncludeOrderDateAsCriterion=0 or SALESORD_HDR.ORDERDATE between @OrderDateRangeStart and @OrderDateRangeEnd)


    In Excel VBA, the code passing the parameters are:

    cmd1.parameters("@OrderDateRangeStart").value = cdate(me.startDate)
    cmd1.parameters("@OrderDateRangeEnd").value = cdate(me.EndDate)

    where cmd1 is a command object, me.startdate is start date field in Excel
    me.enddate is the end date field in Excel.



    The Excel VBA routine works only when the day and month are both 1,
    (e.g. 1/1/2015), when they are other values (e.g. 31/5/2014) , it failed.

    Does any expert here know what's wrong ? Please help.
    Thank you in advance.

    Lepanto
    Last edited by Lepanto; 08-04-15 at 02:05.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try formatting your date using the ISO standard format (YYYY-MM-DD) so your 31/5/2014 date should be expressed as 2014-05-31. The default settings for SQL Server use the United States settings, so they expect MM/DD/YYYY. The ISO format works every time, no matter which locale is selected.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    From memory VBA needs you to assign parameter values for all parameters, in order.

    So if your proc has the following definition:
    Code:
    CREATE PROC foo (
       @a int
     , @b int
     , @c int
    )
    ...
    Then in VBA you have to do:
    Code:
    cmd.parameters("@a").value = 9
    cmd.parameters("@b").value = 3
    cmd.parameters("@c").value = 7
    Any other combination will not work correctly.
    George
    Home | Blog

  4. #4
    Join Date
    Jul 2003
    Location
    Australia
    Posts
    217
    Thank you for your replies, Pat Phelan and George.

    In my stored procedure, should the 2 date parameters be declared as DATE or
    DATETIME type ?

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Again, from memory, VBA/ODBC/OLEDB is happier with the datetime data type, so I would suggest you use this.
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I agree with gvee, use DATETIME unless there is a compelling reason to use another temporal data type like DATE or TIME.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •