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