有一表A,字段DDATE(日期时间)型.现在从2010-01-01 07:55-12:00/2010-01-01 13:00-17:00
至2010-03-01 07:55-12:00/2010-03-01 13:00-17:00
缺少哪一天的记录?(分两组'上午'/'下午')
至2010-03-01 07:55-12:00/2010-03-01 13:00-17:00
缺少哪一天的记录?(分两组'上午'/'下午')
set @startdate='2009-09-28'
set @enddate='2009-10-5'select convert(varchar(10),dateadd(day,number,@startdate),120)
from
master..spt_values
where
datediff(day,dateadd(day,number,@startdate), @enddate)>=0
and number>=0
and type='p'
/*----------
2009-09-28
2009-09-29
2009-09-30
2009-10-01
2009-10-02
2009-10-03
2009-10-04
2009-10-05(8 行受影响)*/
select m.* ,isnull(...) from
(
select '2010-01-01 07:55-12:00' dt union
select '2010-01-02 07:55-12:00' dt union
...
select '2010-03-01 07:55-12:00' dt
) left join
tb n
on m.dt = n.dt