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 (http://blog.SQLAuthority.com)

37 thoughts on “SQL SERVER – UDF – User Defined Function – Get Number of Days in Month

  1. You really don’t need to do the math, you can just get the last day of the month using built in date functions and assign that to your return variable. This is built in for you so you don’t have to worry about leap years or anything

    CREATE FUNCTION [dbo].[udf_GetNumDaysInMonth] ( @myDateTime DATETIME )
    RETURNS INT
    AS
    BEGIN
    DECLARE @rtDate INT
    SET @rtDate = DATEPART(dd, DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,@myDateTime)+1, 0)))
    RETURN @rtDate
    END
    GO

    Like

  2. Hello, Expert

    I’m using Transact-Query, how I select a particular month out of in different years in the database?

    The format column field 2005/08/26…….

    thanks you
    ryan,

    Like

  3. Hi This User defined function will return the no. of days in a month

    CREATE FUNCTION [dbo].[ufn_GetDaysInMonth](
    @CurrentDate DATETIME )
    RETURNS INT
    AS
    BEGIN
    DECLARE @ReturnDays INT
    SET @ReturnDays =
    CASE WHEN MONTH(@CurrentDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
    WHEN MONTH(@CurrentDate) IN (4, 6, 9, 11) THEN 30
    ELSE
    CASE WHEN (YEAR(@CurrentDate) % 4 = 0 AND YEAR(@CurrentDate) % 100 != 0) OR (YEAR(@CurrentDate) % 400 = 0) THEN 29
    ELSE 28
    END
    END
    RETURN @ReturnDays
    END

    –SELECT dbo.ufn_GetDaysInMonth(GETDATE()) No_Of_Days_In_Month
    GO

    Like

  4. Try this one-liner:
    CREATE FUNCTION [dbo].[ufn_GetDaysInMonth](
    @CurrentDate DATETIME )
    RETURNS INT
    AS
    BEGIN
    RETURN DAY(DATEADD(d, -DAY(DATEADD(m,1,@dt)),DATEADD(m,1,@dt)))
    END

    Like

  5. Just a modification to Doug’s posting :

    CREATE FUNCTION [dbo].[ufn_GetDaysInMonth] (@CurrentDate DATETIME )
    RETURNS INT
    AS
    BEGIN
    DECLARE @RetDate INT
    SET @RetDate = DAY(DATEADD(d, -DAY(DATEADD(m,1,@CurrentDate)),DATEADD(m,1,@CurrentDate)))
    RETURN @RetDate
    END

    — SELECT [dbo].[ufn_GetDaysInMonth] (‘2008/02/25′)

    Like

  6. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  7. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 2 Journey to SQL Authority with Pinal Dave

  8. hi.. experts… i need a similar UDF…
    my req is…. if i give 3 param’s to the UDF.. like..
    “month,year and dayofweek” then i should get a list of that day fom the given month,year

    ex. for April 2009 Sunday
    UDFDays(@mm,@yy,@dyw)
    i.e UDFDays(04,2009,’Sunday’)
    then i should get list as..

    6
    13
    20
    27

    i.e… the dates from the month given for weekday.. like for Apil 2009 Saturday..
    i expect..esult as

    5
    12
    19
    26

    etc…
    thanks in advance…

    Like

  9. Re post:… slight mistake frm my side…

    hi.. experts… i need a similar UDF…
    my req is…. if i give 3 param’s to the UDF.. like..
    “month,year and dayofweek” then i should get a list of that day fom the given month,year

    ex. for April 2009 Sunday
    UDFDays(@mm,@yy,@dyw)
    i.e UDFDays(04,2009,’Sunday’)
    then i should get list as..

    5
    12
    19
    26
    i.e… the dates from the month given for weekday.. like for Apil 2009 Saturday..
    i expect..esult as

    4
    11
    18
    25

    etc…
    thanks in advance…

    Like

  10. @gourav, here’s a quick shot:
    CREATE FUNCTION UDFDays(@mm tinyint,@yy smallint,@dyw varchar(9))
    RETURNS TABLE
    AS
    RETURN
    WITH
    Data(The_Date)
    AS
    (
    SELECT CONVERT(smalldatetime, CAST(@mm as varchar(2)) + ‘/01/’ + CAST(@YY as varchar(4)) , 101)
    ),
    Days_In_Month
    AS
    (
    SELECT The_Date, DATEADD(d, -1, DATEADD(m, 1, The_Date)) Next_Month FROM Data
    UNION ALL
    SELECT DATEADD(d, 1, The_Date), Next_Month FROM Days_In_Month WHERE The_Date < Next_Month
    )
    SELECT
    DATEPART(d, The_Date) Day_Number
    FROM
    Days_In_Month
    WHERE
    DATENAME(dw, The_Date) = @dyw;
    GO

    Like

  11. We can write this also instead of case when if someone feels difficulty in using case statements then it will be easy for them using this…………………..
    create function dbo.retdates(@date datetime)
    returns int
    as
    begin
    declare @out int
    if month (@date) in(1,3,5,7,8,10,12)
    set @out=31
    else if month(@date) in(4,6,9,11)
    set @out=30
    else if ((year(@date)%4=0) and (year(@date)%100!=0)) or (year(@date)%400=0)
    set @out=29
    else
    set @out=28
    return @out
    end
    go

    Like

    • @Pavan.

      Major Difference between Function and Procedure.

      Function should return a value where as procedure might or might not.

      You cannot run select statement on procedures where as you can run a select statement on (Table) functions.

      You cannot perform many T-SQL operation inside a function where as you perform many more T-SQL Operations inside a procedure.

      ~Peace.

      Like

      • Hello Friends,
        This site was very informative, i have an issue, would be glad if you can help me.
        The issue that i have to write a user defined funtion

        format of dtActivityStartDate/dtActivityFinishDate: 2010-09-17 14:50:51.150 Note: Both dtActivityStartDate/dtActivityFinishDate

        vcActivityName = Process Request

        usdFuncTimeCalc (vcActivityName,dtActivityStartDate, dtActivityFinishDate)

        i need to calculate time elasped for that type of activity following are the rules:

        (If Process Request is the activity)

        Working Days: Monday through Saturday
        Hours of Operation: 9AM – 5PM

        only working hours of day need to the counted like for example if it is sep 15 11 Am is dtActivityStartDate & Sep 17 is dtActivityFinishDate is 10 Am. then time elapsed is 11am to 5pm on sep 15 , 9 to 5 on sep 16 & 9 to 10 on sep 17 so total should be
        6+ 8 + 1 = 15 hours + minutes.

        format of date time: 2010-09-17 14:50:51.150

        vcActivityName = Process Request

        Like

  12. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 4 of 31 Journey to SQLAuthority

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

    Like

  14. 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

    Like

  15. 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

    Like

  16. 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

    Like

  17. Pingback: SQL SERVER – Weekly Series – Memory Lane – #046 | Journey to SQL Authority with Pinal Dave

  18. 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…….

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s