create table test0523(id int,dtsdate datetime,chra nvarchar(50))
insert into test0523 select 1,'2005-1-1','test'
union all select 2,'2005-1-2','test'
union all select 3,'2005-1-3','aaa'
union all select 4,'2005-1-4','bbb'
set @dtsdate='2005-1-1'
set @chra = 'test'
set @sql='select * from test0523 where chra=' +''''+ @chra+''''+ ' and dtsdate = CONVERT(DATETIME,'+''''+convert(nvarchar(30),@dtsdate ,112)+''''+',120)'
print @sql
exec sp_executesql @sql
create procedure test
(
@dtsdate smalltime,
@chra varchar(50)
)
as
declare @dtsdate smalldatetime
select * from table where chra= @chra and Date =@dtsdate
GO
create procedure test
@dtsdate smalldatetime,
@chra varchar(50)
as
exec ('select * from table where chra=''' + @chra + ''' and Date =CONVERT(DATETIME, ' + @dtsdate + ', 102)' )
---test
declare
@dtsdate smalldatetime,
@chra varchar(50),
@sql nvarchar(4000)
set @dtsdate=getdate()
set @chra = 'test'
set @sql='select * from table where chra=' + @chra + ' and Date = CONVERT(DATETIME,'+convert(nvarchar(30),@dtsdate ,112)+ ',102)'
print @sql
---result
select * from table where chra=test and Date = CONVERT(DATETIME,20050123,102)
如果这样不行则
create procedure test
(
@dtsdate smalldatetime,
@chra varchar(50)
)
as
declare @sql nvarchar(4000)
set @sql='select * from table where chra=' + @chra + ' and Date =CONVERT(DATETIME, ' + convert(nvarchar(30),@dtsdate,112) + ', 102)'
create procedure test
(
@dtsdate smalldatetime,
@chra varchar(50)
)
as
declare @sql nvarchar(4000)
set @sql='select * from table where chra=' + @chra + ' and Date =CONVERT(DATETIME, ' + @dtsdate + ', 102)'