字符串问题

select convert(char(10),passedtime,102),convert(char(8),passedtime,108)
,tollcenterno,tollgateno,roadwayno,
left(cardno, 5),right(cardno, 10),cartype,
charge,carno,
case cardstatus
when '01' then '非法'
when '02' then '正常'
when '03' then '挂失'
when '04' then '临界'
when '05' then '透支'
when '06' then '销卡'
when '07' then '免费'
when '08' then '无卡'
when '09' then '月卡'
when '10' then '优惠'
end
from PassedCars
where passedtime between @startdatetime and @enddatetime
怎样将上面的语句赋值给varchar型字符串(要保留Select语句中的单引号)?

---------------------------------------------------------------

使用两个单引号试一下,如下

select convert(char(10),passedtime,102),convert(char(8),passedtime,108)
,tollcenterno,tollgateno,roadwayno,
left(cardno, 5),right(cardno, 10),cartype,
charge,carno,
case cardstatus
when ''01'' then ''非法''
when ''02'' then ''正常''
when ''03'' then ''挂失''
when '04' then ''临界''
when ''05'' then ''透支''
when ''06'' then ''销卡''
when ''07'' then ''免费''
when ''08'' then ''无卡''
when ''09'' then ''月卡''
when ''10'' then ''优惠''
end
from PassedCars
where passedtime between @startdatetime and @enddatetime
---------------------------------------------------------------

set @sqlcmd='select convert(char(10),passedtime,102),convert(char(8),passedtime,108)
,tollcenterno,tollgateno,roadwayno,
left(cardno, 5),right(cardno, 10),cartype,
charge,carno,
case cardstatus
when ''01'' then ''非法''
when ''02'' then ''正常''
when ''03'' then ''挂失''
when ''04'' then ''临界''
when ''05'' then ''透支''
when ''06'' then ''销卡''
when ''07'' then ''免费''
when ''08'' then ''无卡''
when ''09'' then ''月卡''
when ''10'' then ''优惠''
end
from PassedCars
where passedtime between'''+ @startdatetime +''' and '''+@enddatetime+''''

上面的@startdatetime 我不知道你的格式!

我给你一个特殊的例子:
declare @sqlcmd varchar(1000)
set @sqlcmd='select convert(char(10),passedtime,102),convert(char(8),passedtime,108)
,tollcenterno,tollgateno,roadwayno,
left(cardno, 5),right(cardno, 10),cartype,
charge,carno,
case cardstatus
when ''01'' then ''非法''
when ''02'' then ''正常''
when ''03'' then ''挂失''
when ''04'' then ''临界''
when ''05'' then ''透支''
when ''06'' then ''销卡''
when ''07'' then ''免费''
when ''08'' then ''无卡''
when ''09'' then ''月卡''
when ''10'' then ''优惠''
end
from PassedCars
where passedtime between convert(datetime,'''+ convert(varchar(100),getdate()) +''') and convert(datetime,'''+convert(varchar(100),getdate())+''')'
print @sqlcmd

Published At
Categories with 数据库类
Tagged with
comments powered by Disqus