--
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
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