平台:mssql2005
有表T1,字段描述如下: time1(datatime) time2(datatime)
2008-10-22 2008-10-23
2008-10-22 null
null 2008-10-23
我想通过一个sql语句连接time1和time2查询到这样的结果:
Aftertime
2008-10-22~2008-10-23
2008-10-22~
~2008-10-23即如果连接的时候tiem1或time2中有一个为空那么就单独显示另外的一个日期
谢谢.
有表T1,字段描述如下: time1(datatime) time2(datatime)
2008-10-22 2008-10-23
2008-10-22 null
null 2008-10-23
我想通过一个sql语句连接time1和time2查询到这样的结果:
Aftertime
2008-10-22~2008-10-23
2008-10-22~
~2008-10-23即如果连接的时候tiem1或time2中有一个为空那么就单独显示另外的一个日期
谢谢.
from T1
FROM TABLE
declare @t table (
time1 datetime,
time2 datetime)
insert @t select
'2008-10-22','2008-10-23'
union all select
'2008-10-22', null
union all select
null, '2008-10-23' select isnull(convert(varchar(10),time1,120),'')+'~'+isnull(convert(varchar(10),time2,120),'')
from @t--结果
---------------------
2008-10-22~2008-10-23
2008-10-22~
~2008-10-23(3 行受影响)
INSERT INTO #A SELECT '2008-10-22','2008-10-23'
INSERT INTO #A SELECT '2008-10-22',''
INSERT INTO #A SELECT '','2008-10-23'
SELECT TIME1+'~'+TIME2
FROM #A
DROP TABLE #A
/*2008-10-22~2008-10-23
2008-10-22~
~2008-10-23
select
case when time1 is not null and time2 is not null then convert(varchar(10),time1,120)+'~'+convert(varchar(10),time2,120)
else
isnull(convert(varchar(10),time1,120),'~')+isnull(convert(varchar(10),time2,120),'~') end
from
t1
from T1Aftertime
---------------------
2008-10-22~2008-10-23
2008-10-22~
~2008-10-23(所影响的行数为 3 行)