Conversion query

26 May 2005 3:47 PM

Pete

I have 2 date variables passed into my store procedure as follows

@StartDate datetime,
@EndDate datetime

however I am getting a conversion error when running the command below which
says "Syntax error converting datetime from character string"

PRINT ('INSERT INTO ' + @NewSubsList + '(SubRef)
    SELECT DISTINCT    SubRef
    FROM            Subscriptions
    WHERE        (PubCode = ' + @PubCode + ') AND DateEntered >= ' + @StartDate +
') AND (DateEntered <= ' + @EndDate + ')')

Any suggestions would be welcome.

Thanks

26 May 2005 4:01 PM

Thomas Coleman

You have to explictly cast the datetime values to characters like so

PRINT ('INSERT INTO ' + @NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM   Subscriptions
WHERE (PubCode = ' + @PubCode + ') AND DateEntered >= '
    + Cast(@StartDate As VarChar(20))
    + ') AND (DateEntered <= ' + Cast(@EndDate As VarChar(20)) + ')')

Thomas

Show quoteHide quote

"Pete" <P***@discussions.microsoft.com> wrote in message
news:E78EE524-27E2-47AF-96E4-60B250AFA884@microsoft.com...
>I have 2 date variables passed into my store procedure as follows
>
> @StartDate datetime,
> @EndDate datetime
>
> however I am getting a conversion error when running the command below which
> says "Syntax error converting datetime from character string"
>
> PRINT ('INSERT INTO ' + @NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @PubCode + ') AND DateEntered >= ' + @StartDate +
> ') AND (DateEntered <= ' + @EndDate + ')')
>
> Any suggestions would be welcome.
>
> Thanks