Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Location
    Denver
    Posts
    2

    Unanswered: Dynamic Dates and Stored Procedures

    I was wondering if anyone could help me past a problem that I am having using dynamic dates and stored procedures. I am new to this and have the following procedure written, This particular query will only return data if the variables @sDateFrom and @sDateTo are surrounded by single quotes however, in the stored procedure the single quotes cause the query to fail giving the error "Syntax error converting datetime from character string"

    Any insights would be greatly appreciated.

    Thanks,
    Brent

    ALTER PROC prBuildSelector (
    @sNRW varchar(50),
    @sDateFrom datetime,
    @sDateTo datetime
    )
    AS

    SELECT name,start_date,end_date, sku FROM product
    INNER JOIN section ON product.pfid = section.pfid
    WHERE product.name LIKE '%@sNRW%' AND
    start_date > @sDateFrom
    AND start_date < @sDateTo
    ORDER BY vcfeb10_product.name
    Return

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Which database are you using ?

  3. #3
    Join Date
    Jan 2003
    Posts
    1

    Re: Dynamic Dates and Stored Procedures

    Originally posted by brentb
    I was wondering if anyone could help me past a problem that I am having using dynamic dates and stored procedures. I am new to this and have the following procedure written, This particular query will only return data if the variables @sDateFrom and @sDateTo are surrounded by single quotes however, in the stored procedure the single quotes cause the query to fail giving the error "Syntax error converting datetime from character string"

    Any insights would be greatly appreciated.

    Thanks,
    Brent

    ALTER PROC prBuildSelector (
    @sNRW varchar(50),
    @sDateFrom datetime,
    @sDateTo datetime
    )
    AS

    SELECT name,start_date,end_date, sku FROM product
    INNER JOIN section ON product.pfid = section.pfid
    WHERE product.name LIKE '%@sNRW%' AND
    start_date > @sDateFrom
    AND start_date < @sDateTo
    ORDER BY vcfeb10_product.name
    Return

    EXEC('SELECT name,start_date,end_date, sku FROM product
    INNER JOIN section ON product.pfid = section.pfid
    WHERE product.name LIKE ''%' + @sNRW + '%'' AND
    start_date > @sDateFrom
    AND start_date < @sDateTo
    ORDER BY vcfeb10_product.name')

Posting Permissions

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