SQLServer获取时间日期 相关 函数

连续日期

ALTER FUNCTION [dbo].[fun_getDateListByStartAndEnd]
(	
   @startTm varchar(30),
	 @endTm varchar(30)
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT  DATEADD(dd, number, @startTm) AS MYTM,cast(MONTH(DATEADD(dd, number, @startTm)) as varchar)+'月'+cast(DAY(DATEADD(dd, number, @startTm)) as varchar)+'日' AS MYDAY
    FROM  master.dbo.spt_values
    WHERE  type = 'P' AND number <= DATEDIFF(DAY,CONVERT(varchar(20), @startTm, 23),CONVERT(varchar(20), @endTm, 23))
)

连续月

ALTER FUNCTION [dbo].[fun_getMonth]
(	
   @startTm varchar(30),
	 @endTm varchar(30)
-- 	 @stcd varchar(8)
)
RETURNS TABLE 
AS
RETURN 
(
		SELECT  DATEADD(mm, number, @startTm) AS MYTM,
DateName(month,DATEADD(mm, number,@startTm))+'月' AS MYDAY
FROM  master.dbo.spt_values  WHERE  type = 'P' AND number<= DATEDIFF(mm,CONVERT(varchar(20),@startTm, 23),CONVERT(varchar(20), @endTm, 23))
)

 其他自行改造

输入年获取当前年所有月份

ALTER FUNCTION [dbo].[fun_getMonthByYear]
(	
   @year varchar(4)
)
RETURNS TABLE 
AS
RETURN 
(
select SUBSTRING(CONVERT(NVARCHAR(10), DATEADD(MONTH, number, @year),120),1,7) as YF
from master..spt_values 
where type='P' and number <= 11
)

 输入 2021-01获取当月天数

ALTER FUNCTION [dbo].[fun_getDayByMonth]
(	
   @tm varchar(30)
-- 	 @stcd varchar(8)
)
RETURNS TABLE 
AS
RETURN 
(
		select convert(varchar(10),dateadd(dd,number,convert(varchar(8), @tm+'-01',120)+'01'),120) as DAY
from master..spt_values
where type='P'
and dateadd(dd,number,convert(varchar(8),@tm+'-01',120)+'01')<=dateadd(dd,-1,convert(varchar(8),dateadd(mm,1,@tm+'-01'),120)+'01') 
)