-- 1.删除测试用表 IF OBJECT_ID (N ' Test ' , N ' U ' ) IS NOT NULL DROP TABLE Test -- 2.建立测试表,并填充测试数据 SELECT * INTO Test FROM ( SELECT CONVERT ( DATETIME , ' 2008-1-1 ' , 120 ) AS dt UNION ALL SELECT ' 2008-1-15 ' UNION ALL SELECT ' 2008-1-31 ' UNION ALL SELECT ' 2008-2-1 ' UNION ALL SELECT ' 2008-2-15 ' UNION ALL SELECT ' 2008-2-29 ' UNION ALL SELECT ' 2007-2-1 ' UNION ALL SELECT ' 2007-2-15 ' UNION ALL SELECT ' 2007-2-28 ' UNION ALL SELECT ' 2007-4-1 ' UNION ALL SELECT ' 2007-4-15 ' UNION ALL SELECT ' 2007-4-30 ' ) T -- 3.根据日期计算当月有多少天 SELECT * -- 当月天数=32-[(上月最后最后一天+32天)的日期值] -- e.g. '2008-1':32-day('2008-2-1') '2008-2':32-day('2008-3-3') , ds1 = 32 - DAY (dt - DAY (dt) + 32 ) -- 当月天数=当月最后一天的日期值 -- e.g. '2008-1':day('2008-1-31') '2008-2':day('2008-2-29') , ds2 = DAY ( DATEADD (mm, 1 ,dt) - DAY ( DATEADD (mm, 1 ,dt))) , ds3 = DAY ( DATEADD (mm, MONTH (dt), dt - DATEPART (dy,dt))) , ds4 = DAY ( DATEADD (d, - 1 , CONVERT ( VARCHAR ( 8 ), DATEADD (m, 1 ,dt), 120 ) + ' 01 ' )) -- 当月天数=本月一号到下月一号的天数差值 -- e.g. datediff(d, '2008-1-1', '2008-2-1') , ds5 = DATEDIFF (d, DATEADD (dd, 1 - DAY (dt),dt), DATEADD (mm, 1 , DATEADD (dd, 1 - DAY (dt),dt))) , ds6 = DATEDIFF (d, DATEADD (m, DATEDIFF (m, 0 , dt), 0 ), DATEADD (m, datediff (m, 0 , dt) + 1 , 0 )) , ds7 = DATEDIFF (d, CONVERT ( VARCHAR ( 8 ), dt, 120 ) + ' 01 ' , CONVERT ( VARCHAR ( 8 ), DATEADD (m, 1 ,dt), 120 ) + ' 01 ' ) from Test /**/ /*dt ds1 ds2 ds3 ds4 ds5 ds6 ds7----------------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------2008-01-01 00:00:00.000 31 31 31 31 31 31 312008-01-15 00:00:00.000 31 31 31 31 31 31 312008-01-31 00:00:00.000 31 31 31 31 31 31 312008-02-01 00:00:00.000 29 29 29 29 29 29 292008-02-15 00:00:00.000 29 29 29 29 29 29 292008-02-29 00:00:00.000 29 29 29 29 29 29 292007-02-01 00:00:00.000 28 28 28 28 28 28 282007-02-15 00:00:00.000 28 28 28 28 28 28 282007-02-28 00:00:00.000 28 28 28 28 28 28 282007-04-01 00:00:00.000 30 30 30 30 30 30 302007-04-15 00:00:00.000 30 30 30 30 30 30 302007-04-30 00:00:00.000 30 30 30 30 30 30 30(12 row(s) affected)*/ -- 4.删除测试表 DROP TABLE Test