sql server如何计算一个月最多有多少天
具体方法如下:--1.删除测试用表IFOBJECT_ID(N'Test',N'U')ISNOTNULLDROPTABLETest--2.建立测试表,并填充测试数据SELECT*INTOTestFROM(SELECTCONVERT(DATETIME,'2008-1-1',120)ASdtUNIONALLSELECT'2008-1-15'UNIONALLSELECT'2008-1-31'UNIONALLSELECT'2008-2-1'UNIONALLSELECT'2008-2-15'UNIONALLSELECT'2008-2-29'UNIONALLSELECT'2007-2-1'UNIONALLSELECT'2007-2-15'UNIONALLSELECT'2007-2-28'UNIONALLSELECT'2007-4-1'UNIONALLSELECT'2007-4-15'UNIONALLSELECT'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')fromTest