本帖最後由 chieh-zz 於 2014-10-20 08:27 AM 編輯
SQL Server 日曆製作~
- DECLARE @Date DATETIME
- Set @Date = GetDate()
- DECLARE @Start DATETIME,@End DATETIME
- DECLARE @Index INT
- SET @Start = DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0)
- SET @End = DATEADD(MONTH,1,@Start)
- SET @Index = DATEDIFF(DAY,-1,@Start)%7 - 1;
- SET @Start = DATEADD(mm,DATEDIFF(mm,0,@Date),0)
- SET @End = DATEADD(mm,1,@Start) - 1
- SET @Index= DATEDIFF(day,0,@Start)%7
- ;WITH temp(date,row,col) AS
- (
- SELECT date=1,row=@Index/7+1,col=@Index%7+1
- UNION ALL
- SELECT date=date+1,row=(@Index+date)/7+1,col=(@Index+date)%7+1
- FROM temp
- WHERE date <= DATEDIFF(DAY,@Start,@End)
- )
- SELECT ISNULL(CONVERT(CHAR(2),[1]),'') AS 一,
- ISNULL(CONVERT(CHAR(2),[2]),'') AS 二,
- ISNULL(CONVERT(CHAR(2),[3]),'') AS 三,
- ISNULL(CONVERT(CHAR(2),[4]),'') AS 四,
- ISNULL(CONVERT(CHAR(2),[5]),'') AS 五,
- ISNULL(CONVERT(CHAR(2),[6]),'') AS 六,
- ISNULL(CONVERT(CHAR(2),[7]),'') AS 日
- FROM temp
- PIVOT
- (
- MAX(date) FOR col IN ([2],[3],[4],[5],[6],[7],[1])
- ) AS B
複製代碼
... |