怎么查找遗漏日期

如何查找遗漏日期
我这里有个很大的表，按照数据顺序排列如下

20010101 
20010402 
20010523 
20010623 
20010809 
20010911 
20011112 
20011214 
20020219 
20020326 
20020505 
20020611
20020915
20021230 

上表中有不少月份缺失
如何把这些缺失的找出来
谢谢了~~

 

[解决办法]

SQL code

declare @T table (col datetime)insert into @Tselect '20010101' union allselect '20010402' union allselect '20010523' union allselect '20010623' union allselect '20010809' union allselect '20010911' union allselect '20011112' union allselect '20011214' union allselect '20020219' union allselect '20020326' union allselect '20020505' union allselect '20020611' union allselect '20020915' union allselect '20021230'select a.col as c1,b.col as c2 from @T a right join (select dateadd(month,number,'20010101') as col from master..spt_values where type='p' and number < 24) bon convert(varchar(7),a.col,120)=convert(varchar(7),b.col,120)/*c1                      c2----------------------- -----------------------2001-01-01 00:00:00.000 2001-01-01 00:00:00.000NULL                    2001-02-01 00:00:00.000NULL                    2001-03-01 00:00:00.0002001-04-02 00:00:00.000 2001-04-01 00:00:00.0002001-05-23 00:00:00.000 2001-05-01 00:00:00.0002001-06-23 00:00:00.000 2001-06-01 00:00:00.000NULL                    2001-07-01 00:00:00.0002001-08-09 00:00:00.000 2001-08-01 00:00:00.0002001-09-11 00:00:00.000 2001-09-01 00:00:00.000NULL                    2001-10-01 00:00:00.0002001-11-12 00:00:00.000 2001-11-01 00:00:00.0002001-12-14 00:00:00.000 2001-12-01 00:00:00.000NULL                    2002-01-01 00:00:00.0002002-02-19 00:00:00.000 2002-02-01 00:00:00.0002002-03-26 00:00:00.000 2002-03-01 00:00:00.000NULL                    2002-04-01 00:00:00.0002002-05-05 00:00:00.000 2002-05-01 00:00:00.0002002-06-11 00:00:00.000 2002-06-01 00:00:00.000NULL                    2002-07-01 00:00:00.000NULL                    2002-08-01 00:00:00.0002002-09-15 00:00:00.000 2002-09-01 00:00:00.000NULL                    2002-10-01 00:00:00.000NULL                    2002-11-01 00:00:00.0002002-12-30 00:00:00.000 2002-12-01 00:00:00.000*/
[解决办法]
SQL code
CREATE TABLE TABLE5(DocDate DATETIME)INSERT INTO TABLE5SELECT '20010101' UNION ALLSELECT '20010402' UNION ALLSELECT '20010523' UNION ALLSELECT '20010623' UNION ALLSELECT '20010809' UNION ALLSELECT '20010911' UNION ALLSELECT '20011112' UNION ALLSELECT '20011214' UNION ALLSELECT '20020219' UNION ALLSELECT '20020326' UNION ALLSELECT '20020505' UNION ALLSELECT '20020611' UNION ALLSELECT '20020915' UNION ALLSELECT '20021230'  DECLARE @MINMONTH DATETIMEDECLARE @MAXMONTH DATETIMEDECLARE @TMEPMONTH DATETIMESELECT @MINMONTH =MIN(DocDate),@MAXMONTH =MAX(DocDate) FROM TABLE5  CREATE TABLE #TABLE6(DocDate DATETIME)WHILE DATEDIFF(MONTH,@MINMONTH,@MAXMONTH)>0BEGINSET @MINMONTH =DATEADD(MONTH,1,@MINMONTH)IF NOT EXISTS(SELECT 1 FROM TABLE5 WHERE CONVERT(CHAR(7),DocDate,111) = CONVERT(CHAR(7),@MINMONTH,111) )BEGININSERT INTO #TABLE6SELECT @MINMONTHENDENDSELECT * FROM #TABLE6DROP TABLE #TABLE6/*DocDate-----------------------2001-02-01 00:00:00.0002001-03-01 00:00:00.0002001-07-01 00:00:00.0002001-10-01 00:00:00.0002002-01-01 00:00:00.0002002-04-01 00:00:00.0002002-07-01 00:00:00.0002002-08-01 00:00:00.0002002-10-01 00:00:00.0002002-11-01 00:00:00.000(10 行受影响)*/