Professional Web Applications Themes

Simple Stored Procedure Question! - Microsoft SQL / MS SQL Server

Dear All I am new to SQL Server so this may seem like an obvious question, but why does running the stored procedure below come up with the error "error in trying to convert varchar to smalldatetime". I would presume that the error is (StartDate = '1/1/03', EndDate = '31/12/03') in the use of ''. I am more used to Access and its use of # around dates. The field added is a smalldatetime field. CREATE PROCEDURE uSP_ThisYearsNewEntrants StartDate varchar(15), EndDate varchar(15) AS SELECT Added FROM dbo.tblPatients WHERE (Added BETWEEN StartDate AND EndDate) GO EXECUTE uSP_ThisYearsNewEntrants StartDate = '1/1/03', EndDate ...

  1. #1

    Default Simple Stored Procedure Question!

    Dear All

    I am new to SQL Server so this may seem like an obvious question, but why
    does running the stored procedure below come up with the error "error in
    trying to convert varchar to smalldatetime". I would presume that the error
    is (StartDate = '1/1/03', EndDate = '31/12/03') in the use of ''. I am
    more used to Access and its use of # around dates.

    The field added is a smalldatetime field.
    CREATE PROCEDURE uSP_ThisYearsNewEntrants
    StartDate varchar(15),
    EndDate varchar(15)
    AS
    SELECT Added
    FROM dbo.tblPatients
    WHERE (Added BETWEEN StartDate AND EndDate)
    GO

    EXECUTE uSP_ThisYearsNewEntrants StartDate = '1/1/03', EndDate =
    '31/12/03'
    GO

    Thanks for the help.

    Alastair MacFarlane






    Alastair Guest

  2. #2

    Default Re: Simple Stored Procedure Question!

    Hi Alastair.

    It's because SQL Server is failing to convert the string value '31/12/03' to
    a meaningful date (it's interpreting the date as the 12th of the 31st, 2003)

    Various settings play a role in how SQL Server interprets strings into dates
    & vice versa - eg (system regional settings, user profile regional settings
    & SQL Server configuration settings).

    The best approach is to use the following format for providing date
    strings - "yyyymmdd" as this always works, regardless of any of the above
    settings. This way, your T-SQL code will always work.

    eg: EXECUTE uSP_ThisYearsNewEntrants StartDate = '20030101', EndDate =
    '20031231'

    Another option is to use the SET DATEFORMAT session setting - you can read
    up on that in SQL Server Books Online.

    HTH

    Regards,
    Greg Linwood
    SQL Server MVP

    "Alastair MacFarlane" <co.uk> wrote in message
    news:#phx.gbl... 
    error 


    Greg Guest

  3. #3

    Default Re: Simple Stored Procedure Question!

    Hi Greg,

    Thanks for the reply and the comprehensive answer. I appreciate your time.
    If you don't mind, can I ask you a supplementary question based on your
    response to my question?

    When I call the procedure from say, ADO, do I need to convert the parameter
    to the format ('20030101') or would it just be OK to pass a valid date from
    the called application, be it VB or a web site? If I do would the Cstr
    (converts string) function do?

    I appreciate your response or anyone else to this question.

    Thanks

    Alastair MacFarlane

    "Greg Linwood" <com> wrote in message
    news:phx.gbl... 
    to 
    2003) 
    dates 
    settings [/ref]
    why 
    > error 
    >
    >[/ref]


    Alastair Guest

  4. #4

    Default Re: Simple Stored Procedure Question!

    Hi Alastair

    If the stored proc didn't have varchar(15) for the the parameter types & had
    smalldatetime instead, it would simplify the whole thing. Are you able to
    re-design the stored proc to, say:

    CREATE PROCEDURE uSP_ThisYearsNewEntrants
    StartDate smalldatetime
    EndDate smalldatetime
    AS
    SELECT Added
    FROM dbo.tblPatients
    WHERE (Added BETWEEN StartDate AND EndDate)
    GO

    Then you could pass the parameter from ADO naturally as a datetime type
    without any need to worry about conversion formats, regional settings etc..

    If you could do this, it would work well for either VB / Web.

    Regards,
    Greg Linwood
    SQL Server MVP

    "Alastair MacFarlane" <co.uk> wrote in message
    news:phx.gbl... 
    parameter 
    from [/ref]
    '31/12/03' 
    > 2003) 
    > dates 
    > settings [/ref]
    above [/ref]
    read [/ref]
    > why [/ref][/ref]
    in 
    > > error [/ref][/ref]
    am 
    > >
    > >[/ref]
    >
    >[/ref]


    Greg Guest

  5. #5

    Default Re: Simple Stored Procedure Question!

    Greg,

    Once again thanks for your replay and help!

    Alastair MacFarlane

    "Greg Linwood" <com> wrote in message
    news:%phx.gbl... 
    had 
    etc.. [/ref]
    time. 
    > parameter 
    > from [/ref]
    > '31/12/03' 
    > > 2003) 
    > > dates 
    > > settings [/ref]
    > above [/ref][/ref]
    = [/ref]
    > read [/ref][/ref]
    but [/ref][/ref]
    "error [/ref][/ref]
    the [/ref][/ref]

    > >
    > >[/ref]
    >
    >[/ref]


    Alastair Guest

Similar Threads

  1. Simple Stored Procedure Error
    By ism in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 29th, 11:16 PM
  2. simple stored procedure
    By dave in forum ASP Database
    Replies: 2
    Last Post: April 6th, 04:17 PM
  3. Question on Stored procedure
    By Ricky in forum Microsoft SQL / MS SQL Server
    Replies: 11
    Last Post: July 18th, 10:38 PM
  4. Simple Stored Procedure query
    By Paul in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 14th, 08:25 PM
  5. Stored Procedure Newbie Question --
    By Tibor Karaszi in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 9th, 01:26 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139