• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 722
  • Last Modified:

using date variable in dynamic sql statement

hi experts,

this is driving me crazy.  how can i use a date field in a dynamic sql statement?  When I try to run the code below, I get:

Conversion failed when converting datetime from character string.



declare @sql_stmt            nvarchar(2000)
declare      @charge_date      datetime

set @charge_date =  '11/21/2008'

set @sql_stmt = 'select distinct id '
set @sql_stmt = @sql_stmt + 'from table'
set @sql_stmt = @sql_stmt + 'where move_date < '
set @sql_stmt = @sql_stmt + @charge_date
set @sql_stmt = @sql_stmt + ' '


thanks
0
thewayne73
Asked:
thewayne73
1 Solution
 
chapmandewCommented:
declare @sql_stmt            nvarchar(2000)
declare      @charge_date      datetime

set @charge_date =  '11/21/2008'

set @sql_stmt = 'select distinct id '
set @sql_stmt = @sql_stmt + 'from table'
set @sql_stmt = @sql_stmt + 'where move_date < '
set @sql_stmt = @sql_stmt + convert(varchar(10), @charge_date, 101)
set @sql_stmt = @sql_stmt + ' '
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you should consider to avoid dynamic sql.

now, you have 2 solution ( apart from the non-dynamic sql):
method 1 (not recommended): 
set @sql_stmt = 'select distinct id '
set @sql_stmt = @sql_stmt + 'from table'
set @sql_stmt = @sql_stmt + 'where move_date < CONVERT(datetime, '''
set @sql_stmt = @sql_stmt + CONVERT(varchar(10), @charge_date, 120)
set @sql_stmt = @sql_stmt + ''', 120) ' 

method 2 (much easier): 
 
set @sql_stmt = 'select distinct id '
set @sql_stmt = @sql_stmt + 'from table'
set @sql_stmt = @sql_stmt + 'where move_date < @charge_date ' 
... 
exec sp_executesql @sql_stmt, N'@charge_date datetime', @charge_date

Open in new window

0
 
thewayne73Author Commented:
perfect...thanks!!!
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now