可能我表达错意思了,我是从表里面读取一个datetime数据(字段submitTime),比如'2005-05-09 06:08:30'我想用sql语句把它拆分成两段,得到结果是20050509,060830,现在的语句是: select datename(yyyy,submitTime)+datename(mm,submitTime)+datename(dd,submitTime) as time1, case when len(datename(hh,submitTime)) =1 then '0'+ltrim(str(datename(hh,submitTime))) else datename(hh,submitTime) end + case when len(datename(mi,submitTime)) =1 then '0'+ltrim(str(datename(mi,submitTime))) else datename(mi,submitTime) end + case when len(datename(ss,submitTime)) =1 then '0'+ltrim(str(datename(ss,submitTime))) else datename(ss,submitTime) end as time2 from result 因为出现了2005059,和6830这样的结果,所以改为上面这样的土办法,但是想不通的是日期是2005-05-09 会得到结果2005059,想求一种简单的方法...
不用那么复杂,直接这样就可以了。select convert(char(8),submitTime,112) As time1, replace(convert(nvarchar(8),submitTime,108),':','') As time2 from result
那是一样的呀,你把getdate()更改成submitTime,再加上from 表 就行了呀 select replace(convert(varchar(10),submitTime,120),'-','') ,replace(convert(varchar(8),submitTime,108),':','') from result
select replace(convert(varchar(10),getdate(),108),':','')
select replace(convert(varchar(8),getdate(),108),':','')
select
datename(yyyy,submitTime)+datename(mm,submitTime)+datename(dd,submitTime) as time1,
case when len(datename(hh,submitTime)) =1 then '0'+ltrim(str(datename(hh,submitTime)))
else datename(hh,submitTime) end + case when len(datename(mi,submitTime)) =1 then
'0'+ltrim(str(datename(mi,submitTime))) else datename(mi,submitTime) end +
case when len(datename(ss,submitTime)) =1 then '0'+ltrim(str(datename(ss,submitTime)))
else datename(ss,submitTime) end as time2
from result
因为出现了2005059,和6830这样的结果,所以改为上面这样的土办法,但是想不通的是日期是2005-05-09
会得到结果2005059,想求一种简单的方法...
convert(char(8),submitTime,112) As time1,
replace(convert(nvarchar(8),submitTime,108),':','') As time2
from result
select replace(convert(varchar(10),submitTime,120),'-','')
,replace(convert(varchar(8),submitTime,108),':','')
from result