给出一个日期为:'2007-01-01'
通过这个信息来得出2007年1月的天数为31
思路
在日期上加1個月,再減一天,就是這個月的最後一天(也就是天數)
declare
@date
datetime
set @date = getdate ()
select DAY ( dateadd ( day , - 1 , convert ( char ( 07 ), dateadd ( month , 1 , @date ), 120 ) + ' -01 ' ))
set @date = getdate ()
select DAY ( dateadd ( day , - 1 , convert ( char ( 07 ), dateadd ( month , 1 , @date ), 120 ) + ' -01 ' ))
or
declare
@DATE
datetime
set @DATE = ' 2007-01-01 '
SELECT CAST ( DATEADD ( MONTH , DATEDIFF ( MONTH , 0 , @DATE ) + 1 , 0 ) - DATEADD ( MONTH , DATEDIFF ( MONTH , 0 , @DATE ), 0 ) AS INT )
set @DATE = ' 2007-01-01 '
SELECT CAST ( DATEADD ( MONTH , DATEDIFF ( MONTH , 0 , @DATE ) + 1 , 0 ) - DATEADD ( MONTH , DATEDIFF ( MONTH , 0 , @DATE ), 0 ) AS INT )
整理自http://community.csdn.net/Expert/topic/5347/5347201.xml?temp=.2001764