number of days in a month

[nodbek]

how do I print the no of days in a month using query analyzer.

[MAK]

--One way of finding # of days is
declare @monthval varchar(2)
declare @yearval varchar(4)
set @monthval = '02'
set @yearval = '2004'
select datediff(dd,convert(datetime,@yearval+'-'+@monthval+'-01'),
dateadd(mm,1,convert(datetime,@yearval+'-'+@monthval+'-01')))

[bannor]

Working off a trick I saw on here, this should give you the number of days in the current month

select Day(DateAdd(d,-1,DateAdd(m,DateDiff(month,0,getdate())+1,0)))


If you need it as a function/procedure that you could pass any date into, something like this should work

CREATE FUNCTION f_DaysInMonth(@querydate datetime)
RETURNS INT
AS
BEGIN
DECLARE @Days int
SET @Days = Day(DateAdd(d,-1,DateAdd(m,DateDiff,(month,0,@querydate)+1,0)))
RETURN @Days
END
GO

then you could do something like :

select table.datefield, f_DaysInMonth(table.datefield) As DaysInMonth
from table