bhzj52 于 2014.09.25 11:12 提问

exec sp_executesql转换datetime导致精度（秒）丢失

exec sp_executesql @timestr, N'@p1 varchar(50) output,@p2 nvarchar(150) output',@时间 output, @标题 output
select @时间
select @时间 出来的结果就丢失时间秒了！

use wangning868hexun
declare @num int
declare @sqlstr nvarchar( 3000 )
declare @时间 datetime
declare @标题 nvarchar(150)
declare @timestr nvarchar( 1000 )

SELECT *, ROW_NUMBER () OVER (ORDER BY 时间 ) AS RowIndex into ##TableIndex FROM wangning868hexun . dbo . 和讯博客6
--select * from ##TableIndex
set @num =2109
WHILE @num>=2109 and @num<=2120

begin
`set @timestr = 'select @p1=时间,@p2=标题 from ##TableIndex WHERE ##TableIndex. RowIndex=' + cast( @num as varchar )`
`exec sp_executesql @timestr, N'@p1 varchar(50) output,@p2 nvarchar(150) output',@时间 output, @标题 output`
`select @时间'` --选择当行的时间
set @sqlstr = 'bcp "select case when 1=1 then ''

''+标题+''
'' end as title ,'
set @sqlstr = @sqlstr + 'format(时间,''yyyy-MM-dd hh:mm:ss''),case when 1=1 then 出处+''
'' end as source,内容 from ##TableIndex Where ##TableIndex.RowIndex = ' + cast ( @num as varchar)
set @sqlstr = @sqlstr + '" queryout "d:\123\'+ cast(format( @时间,'yyyy-MM-dd hhmmss' ) as varchar(50))+' ' + cast(replace (@标题, ':','：' ) as varchar( 150)) + '.txt" -c -T' --防止标题中有半角冒号
--print @sqlstr
exec master . dbo . xp_cmdshell @sqlstr
set @num =@num + 1
END
drop table ##TableIndex