å
·ä½æ¹æ³å¦ä¸ï¼
--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
温馨提示:答案为网友推荐,仅供参考