SQL写的日历
DECLARE @d smalldatetime
SET @d = '2004-7-1'
SELECT IDENTITY(int,0,1) AS i INTO #t FROM sysobjects
SELECT
MAX(M1) AS M1,
MAX(M2) AS M2,
MAX(M3) AS M3,
MAX(M4) AS M4,
MAX(M5) AS M5,
MAX(M6) AS M6,
MAX(M7) AS M7
FROM
(
SELECT
CASE dw WHEN 1 THEN d END AS m1,
CASE dw WHEN 2 THEN d END AS m2,
CASE dw WHEN 3 THEN d END AS m3,
CASE dw WHEN 4 THEN d END AS m4,
CASE dw WHEN 5 THEN d END AS m5,
CASE dw WHEN 6 THEN d END AS m6,
CASE dw WHEN 7 THEN d END AS m7,
ww
FROM
(
SELECT
DAY(MyDate) AS d,DATEPART(dw,MyDate) AS dw,DATEPART(ww,MyDate) AS ww
FROM
(
SELECT
DATEADD ( d , i, @d ) AS Mydate
FROM #t
WHERE i<32-Day(@d+32-Day(@d))
) AS a
) AS b
) AS c
GROUP BY ww
DROP TABLE #t
SET @d = '2004-7-1'
SELECT IDENTITY(int,0,1) AS i INTO #t FROM sysobjects
SELECT
MAX(M1) AS M1,
MAX(M2) AS M2,
MAX(M3) AS M3,
MAX(M4) AS M4,
MAX(M5) AS M5,
MAX(M6) AS M6,
MAX(M7) AS M7
FROM
(
SELECT
CASE dw WHEN 1 THEN d END AS m1,
CASE dw WHEN 2 THEN d END AS m2,
CASE dw WHEN 3 THEN d END AS m3,
CASE dw WHEN 4 THEN d END AS m4,
CASE dw WHEN 5 THEN d END AS m5,
CASE dw WHEN 6 THEN d END AS m6,
CASE dw WHEN 7 THEN d END AS m7,
ww
FROM
(
SELECT
DAY(MyDate) AS d,DATEPART(dw,MyDate) AS dw,DATEPART(ww,MyDate) AS ww
FROM
(
SELECT
DATEADD ( d , i, @d ) AS Mydate
FROM #t
WHERE i<32-Day(@d+32-Day(@d))
) AS a
) AS b
) AS c
GROUP BY ww
DROP TABLE #t
posted on 2004-07-09 12:25 Goodspeed 阅读(1254) 评论(2) 编辑 收藏 举报