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 -:

And this is the implementation of this function -:

And the Output will be -:

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

(

@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 -: