[轉貼]MS-SQL 產生 日期及星期

原文網址

---#### 設定每周的開始是星期幾
SET DATEFIRST 1

DECLARE @STARTDATE DATETIME,
@ENDDATE DATETIME;

---#### 設定要產生日期的區間
SELECT @STARTDATE = SUBSTRING(CONVERT(varchar(8),GETDATE(),112),1,6) + '01',
@ENDDATE = CONVERT(VARCHAR(8),DATEADD(dd,-1,DATEADD(mm,DATEDIFF(m,0,GETDATE())+1,0)),112);
---#### 產生日期跟星期

;WITH CTETABLE AS (
SELECT [Date] = @STARTDATE
UNION all
SELECT [Date] + 1 FROM CTETABLE WHERE ([Date] < @ENDDATE)
)
SELECT
CONVERT(VARCHAR(8),[Date],112) AS [Date],
CASE datepart(DW, [DATE])
WHEN '1' THEN '星期一'
WHEN '2' THEN '星期二'
WHEN '3' THEN '星期三'
WHEN '4' THEN '星期四'
WHEN '5' THEN '星期五'
WHEN '6' THEN '星期六'
WHEN '7' THEN '星期日' END AS [WeekDay]
FROM CTETABLE