declare @date datetime set @date='2009-02-05' select [day]=convert(varchar,DATEADD(mm,DATEDIFF(mm,0,@date),0)+number,112) from master..spt_values where type='p' and number< datediff(dd,@date,dateadd(mm,1,@date)) /* day 20090201 20090202 20090203 20090204 20090205 20090206 20090207 20090208 20090209 20090210 20090211 20090212 20090213 20090214 20090215 20090216 20090217 20090218 20090219 20090220 20090221 20090222 20090223 20090224 20090225 20090226 20090227 20090228 */ DECLARE @d1 DATETIME, @d2 DATETIME SELECT @d1 = '2010-01-01', @d2 = '2010-05-31' SELECT [day] = CONVERT(VARCHAR, DATEADD(dd, number, @d1), 23) FROM MASTER..spt_values WHERE TYPE = 'p' AND number<= DATEDIFF(dd, @d1, @d2) /* day ------------------------------ 2010-01-01 2010-01-02 2010-01-03 2010-01-04 2010-01-05 2010-01-06 2010-01-07 2010-01-08 2010-01-09 ...... 2010-05-25 2010-05-26 2010-05-27 2010-05-28 2010-05-29 2010-05-30 2010-05-31 (151 行受影响) */