根据日期计算当月有多少天

-- 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          31
2008-01-15 00:00:00.000 31          31          31          31          31          31          31
2008-01-31 00:00:00.000 31          31          31          31          31          31          31
2008-02-01 00:00:00.000 29          29          29          29          29          29          29
2008-02-15 00:00:00.000 29          29          29          29          29          29          29
2008-02-29 00:00:00.000 29          29          29          29          29          29          29
2007-02-01 00:00:00.000 28          28          28          28          28          28          28
2007-02-15 00:00:00.000 28          28          28          28          28          28          28
2007-02-28 00:00:00.000 28          28          28          28          28          28          28
2007-04-01 00:00:00.000 30          30          30          30          30          30          30
2007-04-15 00:00:00.000 30          30          30          30          30          30          30
2007-04-30 00:00:00.000 30          30          30          30          30          30          30

(12 row(s) affected)
*/

-- 4.删除测试表
DROP   TABLE  Test