Home > T - SQL, Tips N Tricks > Total number of days in a month

Total number of days in a month

July 26th, 2009 Arun Mallick Leave a comment Go to comments


Here are some ways described to find out the total no. of days in a given month.

Method 1:

The simplest way of doing this is to get the month and year as input in a datetime variable. Add +1 to the current given month and then subtract -1 to the 1st of the new date derived.

Below code shows the   function [getTotalDaysInMonth]:

SELECT dbo.getLastBusinessDay(GETDATE()) AS TotalDaysInMonth

Output:

Output

The script for the function is given below:

/****** Object:  UserDefinedFunction [dbo].[getTotalDaysInMonth]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
-- =============================================
-- Author:
-- Create date: <25th July, 2009>
-- Description:
-- =============================================
CREATE FUNCTION [dbo].[getTotalDaysInMonth]
(
	-- Add the parameters for the function here
	@anydateofMonth DATETIME
)
RETURNS INT
AS
BEGIN
	-- Declare the return variable here
	DECLARE @totalDaysInMonth INT
	-- Add the T-SQL statements to compute the return value here
 
		DECLARE @givendate DATETIME
		SET @givendate = @anydateofMonth
 
		SET @givendate = STR(YEAR(@givendate)) + '-' + STR(MONTH(@givendate) + 1) + '-01' 
 
		SELECT @totalDaysInMonth = DATEPART(dd, DATEADD(DAY, -1, @givendate))
 
	-- Return the result of the function
	RETURN @totalDaysInMonth
 
END

Method 2:

According to this method, we will set the day part of the given date to 1st date of the month. And then will increment the counter by one till we reach the last date of the given month.

Below code shows the   function [getTotalDaysInMonth]:

SELECT dbo.getLastBusinessDay(GETDATE()) AS TotalDaysInMonth

Output:

Output

The script for the function is given below:

/****** Object:  UserDefinedFunction [dbo].[getTotalDaysInMonth]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
-- =============================================
-- Author:
-- Create date: <25th July, 2009>
-- Description:
-- =============================================
CREATE FUNCTION [dbo].[getTotalDaysInMonth]
(
	-- Add the parameters for the function here
	@anydateofMonth DATETIME
)
RETURNS INT
AS
BEGIN
	-- Declare the return variable here
	DECLARE @totalDaysInMonth INT
	-- Add the T-SQL statements to compute the return value here
 
		DECLARE @givendate DATETIME
		SET @givendate = @anydateofMonth
		SET @totalDaysInMonth = 0
		SET @givendate = STR(YEAR(@givendate)) + '-' + STR(MONTH(@givendate)) + '-01'
		DECLARE @MONTH INT
		SET @MONTH = MONTH(@givendate)
 
		WHILE DATEPART(MM,@givendate) = @MONTH
		 BEGIN
			SET @givendate = DATEADD(dd, 1, @givendate);
			SET @totalDaysInMonth = @totalDaysInMonth + 1
		 END
 
	-- Return the result of the function
	RETURN @totalDaysInMonth
 
END

Method 3:  By implementing simple mathematical formula, as it is either 30 or 31 days, with the exception of February, which can either have 28 or 29 days depending if it is a leap year or not.

Below code shows the   function [getTotalDaysInMonth]:

SELECT dbo.getLastBusinessDay(GETDATE()) AS TotalDaysInMonth

Output:

Output

The script for the function is given below:

/****** Object:  UserDefinedFunction [dbo].[getTotalDaysInMonth]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
-- =============================================
-- Author:		<Arun Kumar Mallick>
-- Create date: <25th July, 2009>
-- Description:	<Find total days of any given month>
-- =============================================
CREATE FUNCTION [dbo].[getTotalDaysInMonth] 
(
	-- Add the parameters for the function here
	@anydateofMonth DATETIME
)
RETURNS INT
AS
BEGIN
	-- Declare the return variable here
	DECLARE @totalDaysInMonth INT
	-- Add the T-SQL statements to compute the return value here
 
		SET @totalDaysInMonth = 
			CASE	WHEN MONTH(@anydateofMonth) in (1, 3, 5, 7, 8, 10, 12) THEN 31
					WHEN MONTH(@anydateofMonth) in (4, 6, 9, 11) THEN 30
					WHEN (YEAR(@anydateofMonth) % 4 = 0 and YEAR(@anydateofMonth) % 100 != 0) or (YEAR(@anydateofMonth) % 400  = 0) THEN 29
				    ELSE 28 
 
			END
 
	-- Return the result of the function
	RETURN @totalDaysInMonth
 
END
Categories: T - SQL, Tips N Tricks
  1. sanjay
    October 10th, 2009 at 10:50 | #1
    Reply | Quote

    Dears i write mannual coding for get total number of days
    see next

    public string GetNumberOfDays(DateTime strStartDate,DateTime strEndDate)
    {
    //Start Date
    string fStartDate = strStartDate.ToString();
    string fEndDate = strEndDate.ToString();
    string[] SD1=fStartDate.Split(” “.ToCharArray());
    string[] ED1 = fEndDate.Split(” “.ToCharArray());
    fStartDate =SD1[0].ToString();
    fEndDate =ED1[0].ToString();
    string[] StartDateArray = fStartDate.Split(”/”.ToCharArray());
    string[] EndDateArray = fEndDate.Split(”/”.ToCharArray());
    Int16 SDate =Convert.ToInt16(StartDateArray[1]);
    Int16 SMonth = Convert.ToInt16(StartDateArray[0]);
    Int16 SYear = Convert.ToInt16(StartDateArray[2]);
    //End Date
    Int16 EDate = Convert.ToInt16(EndDateArray[1]);
    Int16 EMonth = Convert.ToInt16(EndDateArray[0]);
    Int16 EYear = Convert.ToInt16(EndDateArray[2]);
    Int16 TotalDay = 0;
    string strTDays=”";
    Int16 NumDayInMonth =Convert.ToInt16(DateTime.DaysInMonth(SYear, SMonth));
    if (SYear<=EYear)
    {
    if (SMonth <= EMonth)
    {

    while (SDate !=EDate && SMonth!=EMonth)
    {
    if (SDate != NumDayInMonth)
    {
    SDate = Convert.ToInt16(SDate+1) ;
    TotalDay =Convert.ToInt16( TotalDay+1);
    }
    if (SDate == NumDayInMonth)
    {
    SDate = 1;
    SDate =Convert.ToInt16(SDate+1);
    SMonth =Convert.ToInt16(SMonth+1);
    TotalDay = Convert.ToInt16(TotalDay+1);
    }

    }
    strTDays =Convert.ToString(TotalDay+1);

    }
    }
    return strTDays;
    }

  2. Omnibuzz
    October 30th, 2009 at 16:04 | #2
    Reply | Quote

    Method 4, maybe?

    declare @InputDate datetime
    set @InputDate = GetDate()
    select dateadd(mm,datediff(mm,0,@InputDate)+ 1,0)-1 as last_day_of_month

    -Omni

  1. No trackbacks yet.