SQL Server Conversion failed when converting datetime from character string -
i have query fill in date in table that's giving me error message:
conversion failed when converting datetime character string.
below table declaration , query. doing wrong , how can fix it?
create table #inv ( rep_lname nvarchar(50) , rep_fname nvarchar(50) , rep_id nvarchar(50) , rep_email nvarchar(100) , rep_status nvarchar(50) , rep_bu nvarchar(50) , sales_force nvarchar(50) , territory nvarchar(50) , sample_eligibility nvarchar(50) , dm_name nvarchar(100) , phys_inv_date datetime , last_reconciled datetime , inv_type nvarchar(50) , days_since_last_inv int )
i'm trying fill phys_inv_date
field inside cursor so:
open inventory_info fetch next inventory_info @rep_id, @call_date while ( @@fetch_status = 0 ) begin select @ls_sql = 'update #inv set phys_inv_date = case when inventory_type = ''physical'' ' + @call_date + ' else b.inv_date end #inv inner join (select top 1 call_date, rep_id inv_header call_date < ' + @call_date + ' , rep_id = ''' + @rep_id + ''') b on a.rep_id = b.rep_id phys_inv_date null' exec (@ls_sql) fetch next inventory_info @rep_id, @call_date end close inventory_info deallocate inventory_info
what's wrong using sql parameters? there's no obvious reason why need dynamic sql here, , assuming variables correct data types won't need convert anything:
update #inv set phys_inv_date = case when inventory_type = 'physical' @call_date else b.inv_date end #inv inner join ( select top 1 call_date, rep_id inv_header call_date < @call_date , rep_id = @rep_id ) b on a.rep_id = b.rep_id phys_inv_date null
Comments
Post a Comment