trouble using dates in dynamic SQL string

26 Jan 2006 4:22 PM

tommcd24

I have a stored procedure which uses dynamic SQL using parameters from
a Windows app. Two of these parameters are date fields, but the stored
procedure keeps bombing on the section of my string that appends the
date values indicating: "Syntax error converting datetime from
character string."

An example of what I'm trying to do is below... It bombs on the last
line.

DECLARE @StartDate as datetime
DECLARE @EndDate as Datetime

SET @StartDate = '10/1/2005'
SET @EndDate = '12/1/2005'

DECLARE @strSQL as varchar(5000)
    DECLARE @AndNeeded as bit
    SET @AndNeeded = 0

    SET @strSQL = 'SELECT CHEMREP, CUSTNMBR, CUSTNAME, VENDORID, VENDNAME,
ITEMNMBR, ITEMDESC, SOPNUMBE, GLPOSTDT, PRSTADCD, QTY, ' +
        'UnitPrice, EXTPRICE, ITMCLASS, PCKGTYPE, REGION ' +
        'FROM vwCustomerHistoryByInvoice '

        Set @strSQL = @strSQL + 'WHERE GLPOSTDT BETWEEN ' + @StartDate + '
AND ' + @EndDate + ' '

I've tried wrapping triple quotes around the data variables so that a
single quote is included in the string to designated the date field,
but no matter what I try I get the same error message.

Any advice would be appreciated.

26 Jan 2006 4:30 PM

Amiller

you need to cast the dates to varchars to add them to the string

DECLARE @StartDate as datetime
DECLARE @EndDate as Datetime

SET @StartDate = '10/1/2005'
SET @EndDate = '12/1/2005'

DECLARE @strSQL as varchar(5000)
        DECLARE @AndNeeded as bit
        SET @AndNeeded = 0

        SET @strSQL = 'SELECT CHEMREP, CUSTNMBR, CUSTNAME, VENDORID,
VENDNAME,
ITEMNMBR, ITEMDESC, SOPNUMBE, GLPOSTDT, PRSTADCD, QTY, ' +
                'UnitPrice, EXTPRICE, ITMCLASS, PCKGTYPE, REGION ' +
                'FROM vwCustomerHistoryByInvoice '

                Set @strSQL = @strSQL + 'WHERE GLPOSTDT BETWEEN ' +
cast(@StartDate as varchar(10)) + '
AND ' + cast(@EndDate as varchar(10)) + ' '

26 Jan 2006 4:33 PM

Amiller

I gues the question i should have asked was is that the whole SQL
query. If so you to not need to store it in a string to run. You can
have it like this"

SELECT CHEMREP, CUSTNMBR, CUSTNAME, VENDORID, VENDNAME,
ITEMNMBR, ITEMDESC, SOPNUMBE, GLPOSTDT, PRSTADCD, QTY,
UnitPrice, EXTPRICE, ITMCLASS, PCKGTYPE, REGION
FROM vwCustomerHistoryByInvoice
WHERE GLPOSTDT BETWEEN @StartDate AND @EndDate

26 Jan 2006 4:33 PM

Amiller

I guess the question i should have asked was is that the whole SQL
query. If so you to not need to store it in a string to run. You can
have it like this"

SELECT CHEMREP, CUSTNMBR, CUSTNAME, VENDORID, VENDNAME,
ITEMNMBR, ITEMDESC, SOPNUMBE, GLPOSTDT, PRSTADCD, QTY,
UnitPrice, EXTPRICE, ITMCLASS, PCKGTYPE, REGION
FROM vwCustomerHistoryByInvoice
WHERE GLPOSTDT BETWEEN @StartDate AND @EndDate

26 Jan 2006 4:34 PM

Tibor Karaszi

BOL documents in Datatype Precedence that string will be converted to datetime, not the other way
around. So you need to CONVERT your datetime parameters to string before concatenation and make sure
you use a language neutral format.

But the big question is why you use dynamic SQL for this?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

<tommc***@gmail.com> wrote in message news:1138292557.245961.270890@f14g2000cwb.googlegroups.com...
>I have a stored procedure which uses dynamic SQL using parameters from
> a Windows app. Two of these parameters are date fields, but the stored
> procedure keeps bombing on the section of my string that appends the
> date values indicating: "Syntax error converting datetime from
> character string."
>
> An example of what I'm trying to do is below... It bombs on the last
> line.
>
> DECLARE @StartDate as datetime
> DECLARE @EndDate as Datetime
>
> SET @StartDate = '10/1/2005'
> SET @EndDate = '12/1/2005'
>
> DECLARE @strSQL as varchar(5000)
> DECLARE @AndNeeded as bit
> SET @AndNeeded = 0
>
> SET @strSQL = 'SELECT CHEMREP, CUSTNMBR, CUSTNAME, VENDORID, VENDNAME,
> ITEMNMBR, ITEMDESC, SOPNUMBE, GLPOSTDT, PRSTADCD, QTY, ' +
> 'UnitPrice, EXTPRICE, ITMCLASS, PCKGTYPE, REGION ' +
> 'FROM vwCustomerHistoryByInvoice '
>
> Set @strSQL = @strSQL + 'WHERE GLPOSTDT BETWEEN ' + @StartDate + '
> AND ' + @EndDate + ' '
>
> I've tried wrapping triple quotes around the data variables so that a
> single quote is included in the string to designated the date field,
> but no matter what I try I get the same error message.
>
> Any advice would be appreciated.
>

26 Jan 2006 4:44 PM

Alexander Kuznetsov

be specific:

select convert(datetime, '12/1/2005', 101), convert(datetime,
'12/1/2005', 103)

which one do you want SQL Server to use?

26 Jan 2006 5:19 PM

tommcd24

Thanks everyone.

The reason I'm using dynamic SQL is that some of the other parameters
for this proc are IN clause strings ('string1','string2', 'string3')
built from multi-select listboxes in the calling windows app.

26 Jan 2006 7:47 PM

Bob Barrows [MVP]

tommc***@gmail.com wrote:

> Thanks everyone.
>
> The reason I'm using dynamic SQL is that some of the other parameters
> for this proc are IN clause strings ('string1','string2', 'string3')
> built from multi-select listboxes in the calling windows app.

There are other ways to handle that:
http://www.sommarskog.se/arrays-in-sql.html

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

26 Jan 2006 11:14 PM

Erland Sommarskog

(tommc***@gmail.com) writes:

> The reason I'm using dynamic SQL is that some of the other parameters
> for this proc are IN clause strings ('string1','string2', 'string3')
> built from multi-select listboxes in the calling windows app.

Don't do that. There are far better method for this. Look at
http://www.sommarskog.se/arrays-in-sql.html#iter-list-of-strings for a
function that address the problem an easier and more effecient way.

As for your original question, if you would have to use dynamic SQL,
you should have used sp_executesql instead, so you could have passed
the date values as parameters instead. See
http://www.sommarskog.se/dynamic_sql.html#sp_executesql for one
quick example.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx