Here is how we can get, day wise date and their names by
using simple SQL query.
DECLARE @cntr int = 1;
DECLARE @DayNumber int = (DATEDIFF(dd,dateadd(dd, 1-day(GETDATE()),GETDATE()), dateadd(m,1,dateadd(dd, 1-day(GETDATE()),GETDATE()))))
DECLARE @monthNumber int = (MONTH(GETDATE()))
DECLARE @yearNumber int = (YEAR(GETDATE()))
--DECLARE Table to Store the final
output
DECLARE @WeekTable as TABLE (Sr int IDENTITY(1,1), vDate Date, vDayName Varchar(10), DayNumber int)
WHILE @cntr <=@DayNumber
BEGIN
DECLARe @Date as date
SET @Date = CAST(''+CAST(@monthNumber as varchar(2))+''+'/'+''+CAST(@cntr as varchar(2))+''+'/'+''+CAST(@yearNumber as varchar(4))+'' as date)
--Insert record into table
INSERT INTO
@WeekTable (vDate, vDayName, DayNumber)
VALUES (@Date, DATENAME(DW,@Date), DATEPART(DW,@Date))
SET @cntr = @cntr+1
END
--Get the final output
SELECT * FROM @WeekTable
Comments