例如:
请假开始日期 2011-11-01 08:00:00
请假结束日期 2011-11-05 17:30:00 SELECT (convert(varchar(5),请假开始日期,8)+'-'+convert(varchar(5),请假结束日期,8) ) AS 请假时间
FROM 请假登记明细 WHERE 单号='QJDJ1111070001'/*
我只能想到:08:00-17:30
*//*
结果:
日期 请假时间
2011-11-01 08:00-17:30
2011-11-02 08:00-17:30
2011-11-03 08:00-17:30
2011-11-04 08:00-17:30
2011-11-05 08:00-17:30
/* 要怎么写!
请假开始日期 2011-11-01 08:00:00
请假结束日期 2011-11-05 17:30:00 SELECT (convert(varchar(5),请假开始日期,8)+'-'+convert(varchar(5),请假结束日期,8) ) AS 请假时间
FROM 请假登记明细 WHERE 单号='QJDJ1111070001'/*
我只能想到:08:00-17:30
*//*
结果:
日期 请假时间
2011-11-01 08:00-17:30
2011-11-02 08:00-17:30
2011-11-03 08:00-17:30
2011-11-04 08:00-17:30
2011-11-05 08:00-17:30
/* 要怎么写!
declare @start datetime
declare @end datetime
set @start = '2011-05-01'
set @end = '2011-05-07'select dateadd(dd,number,@start) date
from master..spt_values
where [type] = 'p' and number between 0 and datediff(dd,@start,@end)/*********date
-----------------------
2011-05-01 00:00:00.000
2011-05-02 00:00:00.000
2011-05-03 00:00:00.000
2011-05-04 00:00:00.000
2011-05-05 00:00:00.000
2011-05-06 00:00:00.000
2011-05-07 00:00:00.000(7 行受影响)
declare @startdate datetime,@enddate datetime
set @startdate='2011-11-01'
set @enddate='2011-11-05'
;with f as
(
select convert(varchar(10),dateadd(day,number,@startdate),120) as 日期
from
master..spt_values
where
datediff(day,dateadd(day,number,@startdate), @enddate)>=0
and number>=0
and type='p'
)
select
a.日期,b.请假时间
from
f a
cross apply
(SELECT (convert(varchar(5),请假开始日期,8)+'-'+convert(varchar(5),请假结束日期,8) ) AS 请假时间
FROM 请假登记明细 WHERE 单号='QJDJ1111070001')b