Following User Defined Function (UDF) returns the numbers of days in month. It is very simple yet very powerful and full proof UDF.
CREATE FUNCTION [dbo].[udf_GetNumDaysInMonth] ( @myDateTime DATETIME )
RETURNS INT
AS
BEGIN
DECLARE @rtDate INT
SET @rtDate = CASE WHEN MONTH(@myDateTime)
IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN MONTH(@myDateTime) IN (4, 6, 9, 11) THEN 30
ELSE CASE WHEN (YEAR(@myDateTime) % 4 = 0
AND
YEAR(@myDateTime) % 100 != 0)
OR
(YEAR(@myDateTime) % 400 = 0)
THEN 29
ELSE 28 END
END
RETURN @rtDate
END
GO
Run following script in Query Editor:
SELECT dbo.udf_GetNumDaysInMonth(GETDATE()) NumDaysInMonth
GO
ResultSet:
NumDaysInMonth
———————–
31
Reference : Pinal Dave (https://blog.sqlauthority.com)
35 Comments. Leave new
yoga..
How I create UDF for already created table column.
Must any one answer me…
just write a qurey for no.of days in month is simple
see below………
declare @mydate datetime
set @mydate =’2012/02/12′ ;here u can change date as your wish
SELECT day(dateadd(day,-day(@mydate),dateadd(month,1,@mydate))) as numberofdaysthismonth
thank u
Another method is select day(dateadd(month,datediff(month,-1,@mydate),-1))
I created this MS Excel formula that calculates the number of days (D) in a month; M=1 for January… 12 for December; Y = the Gregorian year to add the leap day; you can easily convert it to other languages.
D=ROUNDUP((MOD(MOD(M-2,12)*0.599,1)*6)^0.6,0)+28+
ROUNDUP(MOD(Y,100)/100,0) – ROUNDUP(MOD(Y,400)/400,0) – ROUNDUP(MOD(Y,4)/4,0) + 1
how to call UD function in hibernate?
Totally over engineered solution :select day(dateadd(month,datediff(month,’18991231′,’20130201′),’18991231′)),
There is another simple way to do this :
CREATE FUNCTION [dbo].[ufn_GetDaysInMonth] (@CurrentDate DATETIME )
RETURNS INT
AS
BEGIN
DECLARE @RetDate INT
SET @RetDate = DATEDIFF(d,@CurrentDate,DATEADD (m,1,@CurrentDate))
RETURN @RetDate
END
Here is a very simple way of finding number of days in a month:
declare @date smalldatetime
set @date = ‘2/1/2012’
select DateDiff(Day,@date,DateAdd(month,1,@date))
i want to display number of days in a month say no of mon-5 tue -4 like wise…. i am new to sql i know oly the basics can anyone provide me a solution…….
Hello,
Here is the simple way
select DAY(EOMONTH(‘2012-02-10 23:55:06.290’))
Write a function to find the number of Sundays when you pass a date ?