使用MSSQL透過輸入函數參數(年份及月分)，來產生所有日期的列表清單
create function Func_ListAllDaysByYearMonth(@year int , @month int)
returns @daytemp table (everyday date) --宣告要拋回的資料型態為資料表
as
begin
declare @startdate date = CONVERT(date, CONVERT(varchar,@year) + '-' + CONVERT(varchar, @month) + '-01')
declare @enddate date = DATEADD(DAY, -1, DATEADD(MONTH , 1 , @startdate))
declare @counter int = 0
declare @daycount int = DATEDIFF(DAY , @startdate , @enddate)
while(@counter <= @daycount)
begin
insert into @daytemp values(
DATEADD(DAY , @counter , @startdate)
)
set @counter = @counter + 1
end
return
end
go
--查詢範例
select * from dbo.Func_ListAllDaysByYearMonth(YEAR(GETDATE()) , MONTH(GETDATE()))
go
執行結果：
