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

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 行受影响)*/