Tuesday, February 14, 2012

Dateadd and Dynamic SQL

Hi!
I am trying to pass two variables @.date and @.days in the DATEADD
function but getting syntax error message saying "Syntax error
converting the varchar value 'select dateadd(day, ' to a column of data
type int.". I think I am not using the correct syntax. Can you please
help in correcting it?
Thanks,
declare @.date datetime
declare @.days int
set @.date = '5/19/2005'
set @.days = 60
declare @.S varchar(100)
-- hard coded works
--set @.S = 'select dateadd(day, 60,'''+ convert(varchar(10), @.date, 110)
+ ''')'
set @.S = 'select dateadd(day, ' + @.days + ','' + '''+
convert(varchar(10), @.date, 110) + ''')'
print @.S
*** Sent via Developersdex http://www.examnotes.net ***Any reason you are doing dynamic SQL
Anyway the code below works
declare @.date datetime
declare @.days int
set @.date = '5/19/2005'
set @.days = 60
declare @.S varchar(100)
select @.S = dateadd(day, @.days ,convert(varchar(10), @.date, 110) )
print @.S
http://sqlservercode.blogspot.com/
"Test Test" wrote:

> Hi!
> I am trying to pass two variables @.date and @.days in the DATEADD
> function but getting syntax error message saying "Syntax error
> converting the varchar value 'select dateadd(day, ' to a column of data
> type int.". I think I am not using the correct syntax. Can you please
> help in correcting it?
> Thanks,
> declare @.date datetime
> declare @.days int
> set @.date = '5/19/2005'
> set @.days = 60
> declare @.S varchar(100)
> -- hard coded works
> --set @.S = 'select dateadd(day, 60,'''+ convert(varchar(10), @.date, 110)
> + ''')'
> set @.S = 'select dateadd(day, ' + @.days + ','' + '''+
> convert(varchar(10), @.date, 110) + ''')'
> print @.S
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Yes I need it to be done using dynamic SQL bc. Thanks!
*** Sent via Developersdex http://www.examnotes.net ***|||Try,
set @.S = 'select dateadd(day, ' + ltrim(@.days) + ', @.date)'
AMB
"Test Test" wrote:

> Hi!
> I am trying to pass two variables @.date and @.days in the DATEADD
> function but getting syntax error message saying "Syntax error
> converting the varchar value 'select dateadd(day, ' to a column of data
> type int.". I think I am not using the correct syntax. Can you please
> help in correcting it?
> Thanks,
> declare @.date datetime
> declare @.days int
> set @.date = '5/19/2005'
> set @.days = 60
> declare @.S varchar(100)
> -- hard coded works
> --set @.S = 'select dateadd(day, 60,'''+ convert(varchar(10), @.date, 110)
> + ''')'
> set @.S = 'select dateadd(day, ' + @.days + ','' + '''+
> convert(varchar(10), @.date, 110) + ''')'
> print @.S
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Correction,
set @.S = 'select dateadd(day, ' + ltrim(@.days) + ',''' +
convert(varchar(35), @.date, 126) + ''')'
AMB
"Alejandro Mesa" wrote:
> Try,
> set @.S = 'select dateadd(day, ' + ltrim(@.days) + ', @.date)'
>
> AMB
>
> "Test Test" wrote:
>

No comments:

Post a Comment