Home > SQL Server > Count the days in a calender

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.

Advertisements
  1. Abhishek
    August 6, 2012 at 10:37 pm

    excellent post..

  1. No trackbacks yet.

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

%d bloggers like this: