Journey to SQL Authority with Pinal Dave

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

SELECT dbo.udf_GetNumDaysInMonth(GETDATE()) NumDaysInMonth
GO

Posted in Pinal Dave, SQL, SQL Authority, SQL DateTime, SQL Documentation, SQL Download, SQL Error Messages, SQL Function, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, SQL Utility, T SQL, Technology | 9 Comments

9 Responses to “SQL SERVER - UDF - User Defined Function - Get Number of Days in Month”

1. on September 10, 2007 at 8:38 am Simon Worth

   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

   
   
2. on September 10, 2007 at 9:09 am pinaldave

   Thanks Simon,

   I have used similar logic many times. I just decided to do something different. You can check other datetime related articles here.

   http://blog.sqlauthority.com/tag/sql-datetime/

   Regards,
   Pinal Dave ( http://www.SQLAuthority.com )

   
   
3. on September 12, 2007 at 2:08 pm ryan

   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,

   
   
4. on September 17, 2007 at 3:49 am Munna Sarfraz Ahmad

   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

   
   
5. on September 20, 2007 at 8:37 am Doug

   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

   
   
6. on October 11, 2007 at 8:04 pm Thiru

   Hi pinaldave,

   Thank you very much

   
   
7. on March 10, 2008 at 3:21 pm Vishwanath

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

   
   
8. on July 21, 2008 at 5:53 pm poleswar

   EXCELLENT …:))

   EXACTLY SUITS MY REQUIREMENT

   SUPERB

   THNX A LOT………

   
   
9. on October 13, 2008 at 2:09 pm Mahesh K Rajan

   We have already some built in functions avail, then why should we go behind all those steps…
   Anyway, good logic…
   -Mahesh