Re: Using variables in Dynamic SQL
From: noam (noam_at_rashim.co.il)
Date: 08/31/04
- Next message: Larry Woods: "Query Analyzer - Can you make a 'test run' on query?"
- Previous message: Robert Zirpolo: "Re: Using variables in Dynamic SQL"
- In reply to: Robert Zirpolo: "Re: Using variables in Dynamic SQL"
- Next in thread: Hugo Kornelis: "Re: Using variables in Dynamic SQL"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 31 Aug 2004 18:08:32 +0200
try - set dateformat dmy
"Robert Zirpolo" <robert.zirpolo@phoenixdb.co.uk> wrote in message
news:um#9vX2jEHA.2788@tk2msftngp13.phx.gbl...
> Thank you for the posts and I have now managed to resolve the other
> problems with the syntax and am concentrating on the one line with the
> dateadd function. My revised syntax is,
>
> DECLARE @sql varchar(5000)
> DECLARE @StartDate datetime
> DECLARE @EndDate datetime
> DECLARE @Media nvarchar(500)
> DECLARE @Analysis nvarchar(500)
>
> SELECT @StartDate = '01/07/00'
> SELECT @EndDate = '31/12/04'
> SELECT @Media = '1, 2, 3'
> SELECT @Analysis = '%'
>
> set @sql =
> ''Departing from ''+ '''+convert(char(10),@startdate,103)+''' +'' to ''+
> '''+convert(char(10),@enddate,103)+''' +'' as ''+ ''ReportString''
>
> from booking as b
>
> where (b.disabled = 0)
> and (b.leadsector = 1)
> and (b.mediaid in ('+ @media +')
> and (b.analysiscodeid like '''+ @analysis +''')
> and (se.departureDateTime >= '''+convert(char(10),@startdate,103)+''')
> and (se.departureDateTime < '''+dateadd(day,1,@enddate)+''''
>
> exec(@sql)
>
> As I said above the problem line is,
>
> and (se.departureDateTime < '''+dateadd(day,1,@enddate)+''''
>
> As that produces the error message "Syntax error converting datetime
> from character string."
>
> I am reading through http://www.sommarskog.se/dynamic_sql.html to see if
> this can help me out.
>
> If anybody else knows how to resolve this or a workaround then please
> post a reply.
>
> Thanks
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
- Next message: Larry Woods: "Query Analyzer - Can you make a 'test run' on query?"
- Previous message: Robert Zirpolo: "Re: Using variables in Dynamic SQL"
- In reply to: Robert Zirpolo: "Re: Using variables in Dynamic SQL"
- Next in thread: Hugo Kornelis: "Re: Using variables in Dynamic SQL"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
