SQL SERVER – UDF – User Defined Function – Get Number of Days in Month

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)

Solarwinds
, ,
Previous Post
SQL SERVER – Correlated and Noncorrelated – SubQuery Introduction, Explanation and Example
Next Post
SQL SERVER – 2005 – Start Stop Restart SQL Server From Command Prompt

Related Posts

35 Comments. Leave new

  • Get Month Name using UDF

    CREATE FUNCTION dbo.GetMonthName(@MonthNumber tinyint)
    RETURNS varchar(15)
    AS
    BEGIN
    DECLARE @MonthName varchar(15)
    SET @MonthName = DateName( Month , DateAdd( Month , @MonthNumber , 0 ) – 1 )
    RETURN @MonthName
    END

    Execute it: SELECT dbo.GetMonthName(3)

    Reply
  • yoga..

    How I create UDF for already created table column.
    Must any one answer me…

    Reply
  • 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

    Reply
  • 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

    Reply
  • how to call UD function in hibernate?

    Reply
  • Totally over engineered solution :select day(dateadd(month,datediff(month,’18991231′,’20130201′),’18991231′)),

    Reply
  • 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

    Reply
  • Shalini Solaimuthu
    October 18, 2013 4:59 am

    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))

    Reply
  • 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…….

    Reply
  • Hello,

    Here is the simple way
    select DAY(EOMONTH(‘2012-02-10 23:55:06.290’))

    Reply
  • Write a function to find the number of Sundays when you pass a date ?

    Reply

Leave a Reply

Menu