--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
解决方案 »
- sql怪问题 ??
- 如何对日期进行加减
- 下午写的求某个结点下的叶结点的过程
- 提取数据赋值
- 急求一个sql语句
- 我的书本上没有联接的部分, 在网上又找不到, 联机帮助又好像说得不清不楚, 请问谁有相关资料可以发到我的邮箱吗?
- 关于reporting services报表的部署问题??高手帮帮忙啊!
- 调用一个存储过程返回记录集的问题???
- 不用sql语句,怎样快捷地决定表中列的default的值?!
- 如何把update,insert等等这些触发语句结合运行不会报错!?
- sql server 2000,一个数据库最多能建多少张表,每张表最多能建多少个字段?
- 囧.做人失败..连个SQL 都装不上."安装程序配置服务器失败".大家帮帮我吧.
第一种方法就是你和kelph提出的,+100分,,,
那倒不用,是kelph先回答的,我怎么说也有抄袭的嫌疑,呵呵,给kelph加100吧
SELECT dt,DAY(MAX(dtx)) FROM
(
SELECT dt,CONVERT(VARCHAR(7),dt,120) + '-' + RTRIM(span) dtx FROM test a
CROSS JOIN
(SELECT 28 span UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31) b
) x
WHERE ISDATE(dtx)=1
GROUP BY dt
我提出的新方法,100分哇~~~~
这个跟我以前写的一样,有bug.前一月天数大于下一月时,dateadd(mm,1 加不上去。
2008-1-31 得到的是 2009-2-29
,ds1=32-DAY(dt-DAY(dt)+32)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
/*
dsN ds1
----------- -----------
31 31
31 31
31 31
29 29
29 29
29 29
28 28
28 28
28 28
30 30
30 30
30 30(所影响的行数为 12 行)
*/
--常用的方法你都列出來,看來要100分還得要弄個稀奇八怪的東東出來了
declare @t varchar(07)
set @t='2007-02'
select right(max(@t+t_day),2)
from (select '-28' as t_day union select '-29' union select '-30' union select '-31') T
where isdate(@t+t_day)=1
/*
28
*/set @t='2008-02'
select right(max(@t+t_day),2)
from (select '-28' as t_day union select '-29' union select '-30' union select '-31') T
where isdate(@t+t_day)=1
/*
29
*/set @t='2008-04'
select right(max(@t+t_day),2)
from (select '-28' as t_day union select '-29' union select '-30' union select '-31') T
where isdate(@t+t_day)=1
/*
30a
*/set @t='2008-05'
select right(max(@t+t_day),2)
from (select '-28' as t_day union select '-29' union select '-30' union select '-31') T
where isdate(@t+t_day)=1
/*
31
*/
--当月天数=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)期待詳細註解!
另外2种原理都好理解,能否分析下這種方法的原理?
SQL code--1.删除测试用表IFOBJECT_ID(N'Test', N'U')ISNOTNULLDROPTABLETest--2.建立测试表,并填充测试数据SELECT*INTOTestFROM(SELECTCONVERT(DATETIME,'2008-1-1',120)ASdtUNIONALLSELECT'2008-1-15'UNIONALLSELECT'2008-1-31'UNIONALLSELECT'2008-2-1'UNIONALLSELECT'2008-2-15'UNIONALLSELECT'2008-2-29'UNIONALLSELECT'2007-2-1'UNIONALLSELECT'2007-2-15'UNIONALLSELECT'2007-2-28'UNIONALLSELECT'2007-4-1'UNIONALLSELE…
[/Quote]
槍手大哥的是取月份最後一天的是該月的第幾天。
ds1=32-DAY(dt-DAY(dt)+32)這個是什麽原理?誰來解釋一下?
虛心學習!
乳溝的也看懂了,用年份月份+28、29、30、31,取最大的且為合法日期的天數部分。
槍手大哥的是取月份最後一天的是該月的第幾天。
ds1=32-DAY(dt-DAY(dt)+32)這個是什麽原理?誰來解釋一下?
虛心學習!
[/Quote]
dt-DAY(dt) 不就是上个月的最后一天了呀,这下应该明白了吧
DECLARE @TimecardYear int,@TimecardMonth tinyint
SELECT @TimecardYear=2007,@TimecardMonth=11select DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,CAST(@TimecardYear*10000+@TimecardMonth*100+1 AS VARCHAR(8)))))
IT技术挺全的,不错,鼓励一下:http://www.ciitc.com
dobear_0922 员外散分了大家快来接分!
--当月的周后一天的值
Select Day(DateAdd(mm,1,dt)-Day(DateAdd(mm,1,dt)))
--当月最后一天的日期
Select DateAdd(mm,1,dt)-Day(DateAdd(mm,1,dt))
--每个月第一天的日期
Select getDate()-Day(getDate()-1)
--当月的周后一天的值
Select Day(DateAdd(mm,1,dt)-Day(DateAdd(mm,1,dt)))
--当月最后一天的日期
Select DateAdd(mm,1,dt)-Day(DateAdd(mm,1,dt))
--每个月第一天的日期
Select getDate()-Day(getDate()-1)