Count the days in a calender

We use the date everyday. But, In our organization we need to use the same date in different methods to find the growth of the business. For instance, You manager may ask you current sale amount or current quarter or current year sales total. So, we write different functions to find the number of days in a quarter or year or month using date value.

This blog contains 3 functions that help you find number of days in the month, Quarter, year.

Number of days in a month

Parameter: MonthID = any number between 1 and 12 YearID = Year

CREATE FUNCTION [fn_GetDaysInMonth] ( @month    TINYINT, @year smallint)
RETURNS INT
AS
BEGIN
RETURN CASE WHEN @month IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN @month IN (4, 6, 9, 11) THEN 30
ELSE CASE WHEN (@year % 4    = 0 AND
@year  % 100 != 0) OR
(@year  % 400  = 0)
THEN 29
ELSE 28
END
END
END

Number of Days in a Quarter

Parameter: QuarterID = 1 or 2 or 3 or 4; YearId = Year

CREATE FUNCTION [fn_GetDaysInQuarter](@QuarterID TINYINT, @YearID SMALLINT)
RETURNS TINYINT
AS
BEGIN
IF @QuarterID = 1
IF (@YearID % 400 ) = 0 OR ((@YearID % 4 ) = 0 AND (@YearID % 100 != 0))
RETURN 91
ELSE
RETURN 90
ELSE IF @QuarterID = 2
RETURN 91
ELSE IF @QuarterID = 3 OR @QuarterID = 4
RETURN 92
RETURN 0
END

Number of Days in a Year

Parameter: YearID = 2010, 2012, ….

CREATE FUNCTION [fn_GetDaysInYear](@YearID smallint)
RETURNS smallint
AS
BEGIN
IF  (@YearID % 400 ) = 0 OR ((@YearID % 4 ) = 0 AND (@YearID % 100 != 0))
RETURN  366

RETURN  365
END

Thanks for reading.

About these ads