SQL 一周默认星期一

ALTER  PROC proc_get_days(@startdate DATETIME,@enddate DATETIME,@strtype VARCHAR(10) ='month')
AS 
BEGIN
DECLARE @table TABLE 
(
	startdate DATETIME,
	enddate DATETIME,
	week INT,
	dw VARCHAR(10)
	
)


SET DATEFIRST 1 --默认星期1,是一周开始
WHILE @startdate <=@enddate
BEGIN
INSERT INTO @table(startdate,enddate,week,dw)
SELECT  @startdate,@startdate, DATEPART(week,@startdate),DATENAME(dw, @startdate)

SET @startdate = dateadd(day,1,@startdate)
END 

IF @strtype = 'dw'
BEGIN
	
	SELECT min(startdate) startdate ,max(startdate) enddate, week,DATEPART(year,startdate) year,DATEPART(month,startdate) month
	FROM @table
	GROUP BY week,DATEPART(year,startdate),DATEPART(month,startdate)
	ORDER BY min(startdate)
END 
ELSE
BEGIN
	SELECT min(startdate) startdate ,max(startdate) enddate,0 as week,DATEPART(year,startdate) year,DATEPART(month,startdate) month
	FROM @table
	GROUP BY DATEPART(year,startdate),DATEPART(month,startdate)
	ORDER BY min(startdate)
END

END 





--测试
exce proc_get_days '2015-01-01','2015-12-31','dw'

SQL 默认周一为每天都一天,

每个月破月的处理

截图: