Saturday, May 4, 2013

Return All Dates of Selected Month

This article is dedicated to one of user who asked me how to return all dates of a selected month.
So, here is the function solving this purpose -:

CREATE FUNCTION GetAllDateOfMonth
(   
    @dbDate datetime
)
RETURNS @AllDates TABLE
(
 GenDate datetime not null
)
AS
BEGIN
    DECLARE @monthNo int;
    -- Set Month no of Selected Date
    SET @monthNo = datepart(MM, @dbDate);

    -- Set first day of month
    SET @dbDate = convert(datetime, convert(varchar,datepart(yy,@dbDate)) + '.' + convert(varchar,@monthNo) + '.01 00:00:00');

     WHILE datepart(MM,@dbDate) = @monthNo
     BEGIN
      INSERT INTO @AllDates VALUES (@dbDate);
      SET @dbDate = dateadd(dd, 1, @dbDate);
     END
   
    RETURN
END

And this is the implementation of this function -:

SELECT * FROM [dbo].[GetAllDateOfMonth] (GETDATE())

And the Output will be -: