--1.删除测试用表
IF OBJECT_ID(N'Test', N'U') IS NOT NULL
DROP TABLE Test
--2.建立测试表，并填充测试数据
SELECT * INTO Test
FROM
(
SELECT CONVERT(DATETIME, '2008-1-1', 120) AS dt UNION ALL SELECT '2008-1-15' UNION ALL SELECT '2008-1-31'
UNION ALL SELECT '2008-2-1' UNION ALL SELECT '2008-2-15' UNION ALL SELECT '2008-2-29'
UNION ALL SELECT '2007-2-1' UNION ALL SELECT '2007-2-15' UNION ALL SELECT '2007-2-28'
UNION ALL SELECT '2007-4-1' UNION ALL SELECT '2007-4-15' UNION ALL SELECT '2007-4-30'
) T
--3.根据日期计算当月有多少天
SELECT *
--当月天数=32-[(上月最后最后一天+32天)的日期值]
--e.g. '2008-1':32-day('2008-2-1') '2008-2':32-day('2008-3-3')
, ds1=32-DAY(dt-DAY(dt)+32)
--当月天数=当月最后一天的日期值
--e.g. '2008-1':day('2008-1-31') '2008-2':day('2008-2-29')
, ds2=DAY(DATEADD(mm,1,dt)-DAY(DATEADD(mm,1,dt)))
, ds3=DAY(DATEADD(mm, MONTH(dt), dt-DATEPART(dy,dt)))
, ds4=DAY(DATEADD(d, -1, CONVERT(VARCHAR(8), DATEADD(m,1,dt), 120)+'01'))
--当月天数=本月一号到下月一号的天数差值
--e.g. datediff(d, '2008-1-1', '2008-2-1')
, ds5=DATEDIFF(d, DATEADD(dd,1-DAY(dt),dt), DATEADD(mm, 1, DATEADD(dd,1-DAY(dt),dt)))
, ds6=DATEDIFF(d, DATEADD(m, DATEDIFF(m, 0, dt), 0), DATEADD(m, datediff(m, 0, dt)+1, 0))
, ds7=DATEDIFF(d, CONVERT(VARCHAR(8), dt, 120)+'01', CONVERT(VARCHAR(8), DATEADD(m,1,dt), 120)+'01')
from Test
/**//*
dt ds1 ds2 ds3 ds4 ds5 ds6 ds7
----------------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2008-01-01 00:00:00.000 31 31 31 31 31 31 31
2008-01-15 00:00:00.000 31 31 31 31 31 31 31
2008-01-31 00:00:00.000 31 31 31 31 31 31 31
2008-02-01 00:00:00.000 29 29 29 29 29 29 29
2008-02-15 00:00:00.000 29 29 29 29 29 29 29
2008-02-29 00:00:00.000 29 29 29 29 29 29 29
2007-02-01 00:00:00.000 28 28 28 28 28 28 28
2007-02-15 00:00:00.000 28 28 28 28 28 28 28
2007-02-28 00:00:00.000 28 28 28 28 28 28 28
2007-04-01 00:00:00.000 30 30 30 30 30 30 30
2007-04-15 00:00:00.000 30 30 30 30 30 30 30
2007-04-30 00:00:00.000 30 30 30 30 30 30 30
(12 row(s) affected)
*/
--4.删除测试表
DROP TABLE Test 发表于 @ 2008年05月05日 14:09:13|评论(loading...)|编辑