怎样计算当年每个月份的第一天或者最后一天
我现在只会查询
1.计算当前一个月的第一天日期 结果:2011.11.01
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
或
select dateadd(dd,-datepart(dd,getdate())+1,getdate())
2.计算本月的最后一天 结果:2011.11.30
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
或
select dateadd(dd,-datepart(dd,getdate()) ,dateadd(mm,1,getdate()))
可是根据当前时间所在年度
比如,现在是2011年
通过语句可以根据系统时间查询输出
2011.01.01、2011.02.01、2011.03.01、2011.04.01、2011.05.01、2011.06.01、
2011.07.01、2011.08.01、2011.09.01、2011.10.01、2011.11.01、2011.12.01
到了明年,同样的语句可以输出
2012.01.01、2012.02.01、2012.03.01、2012.04.01、2012.05.01、2012.06.01、
2012.07.01、2012.08.01、2012.09.01、2012.10.01、2012.11.01、2012.12.01
抑或可以计算每个月的最后一天,比如
2011.01.31、2011.02.28、2011.03.31、2011.04.30、2011.05.31、2011.06.30....
每次都要求输出一个日期即可还请大家赐教 散分....
[解决办法]
create table #t(ymd datetime)declare @year intdeclare @i intset @year = 2011set @i = 1while @i <= 12begininsert into #tSELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,convert(datetime,ltrim(@year)+right(100+@i,2)+'01'))+1, 0)) as ymdset @i = @i + 1endselect * from #tdrop table #t/**************ymd-----------------------2011-01-31 23:59:59.9972011-02-28 23:59:59.9972011-03-31 23:59:59.9972011-04-30 23:59:59.9972011-05-31 23:59:59.9972011-06-30 23:59:59.9972011-07-31 23:59:59.9972011-08-31 23:59:59.9972011-09-30 23:59:59.9972011-10-31 23:59:59.9972011-11-30 23:59:59.9972011-12-31 23:59:59.997(12 行受影响)
[解决办法]
每个月也是一样的啊
用master..spt_values表来构造时间。
[解决办法]
DECLARE @dt DATETIMESET @dt=GETDATE()SET @dt=RTRIM(YEAR(@dt))+'0101'SELECT DATEADD(m,number,@dt) AS StartDate,DATEADD(m,number+1,@dt)-1 AS EndDateFROM master.dbo.spt_values WHERE type='P' AND DATEADD(m,number,@dt)<DATEadd(yy,1,@dt)/*StartDate EndDate2011-01-01 00:00:00.000 2011-01-31 00:00:00.0002011-02-01 00:00:00.000 2011-02-28 00:00:00.0002011-03-01 00:00:00.000 2011-03-31 00:00:00.0002011-04-01 00:00:00.000 2011-04-30 00:00:00.0002011-05-01 00:00:00.000 2011-05-31 00:00:00.0002011-06-01 00:00:00.000 2011-06-30 00:00:00.0002011-07-01 00:00:00.000 2011-07-31 00:00:00.0002011-08-01 00:00:00.000 2011-08-31 00:00:00.0002011-09-01 00:00:00.000 2011-09-30 00:00:00.0002011-10-01 00:00:00.000 2011-10-31 00:00:00.0002011-11-01 00:00:00.000 2011-11-30 00:00:00.0002011-12-01 00:00:00.000 2011-12-31 00:00:00.000*/
[解决办法]
http://topic.csdn.net/u/20090906/17/a91cd31e-abf7-4190-8486-c5491aa63c71.html?seed=1380177511&r=59598572#r_59598572
[解决办法]
SQL Server 日期算法一周的第一天select @@DATEFIRST一个月的第一天 select dateadd(mm,datediff(mm,0,getdate()),0)本周的星期一 select dateadd(wk,datediff(wk,0,getdate()),0)一年的第一天 SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) 季度的第一天 SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) 当天的零时SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) 上个月的最后一天 :本月第一天减2ms.SELECT dateadd(ms,-2,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) 本月的最后一天 SELECT dateadd(ms,-2,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) 本月的第一个星期一 去掉时分秒 DATEADD(day, DATEDIFF(day,0,getdate()), 0) 显示星期几 select datename(weekday,getdate()) 如何取得某个月的天数 SELECT Day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))) 判断是否闰年: SELECT case day(dateadd(mm, 2, dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)))) when 28 then '平年' else '闰年' end 一个季度多少天 declare @m tinyint,@time smalldatetime select @m=month(getdate()) select @m=case when @m between 1 and 3 then 1 when @m between 4 and 6 then 4 when @m between 7 and 9 then 7 else 10 end select @time=datename(year,getdate())+'-'+convert(varchar(10),@m)+'-01' select datediff(day,@time,dateadd(mm,3,@time))
[解决办法]
唉~~
[解决办法]
declare @sdate datetime declare @edate datetime set @sdate = '2009-8-30' set @edate = '2009-9-5' select dateadd(dd,num,@sdate) from (select isnull((select count(1) from sysobjects where id <t.id),0) as num from sysobjects t) a where dateadd(dd,num,@sdate) <=@edate /* ------------------------------------------------------ 2009-08-30 00:00:00.000 2009-08-31 00:00:00.000 2009-09-01 00:00:00.000 2009-09-02 00:00:00.000 2009-09-03 00:00:00.000 2009-09-04 00:00:00.000 2009-09-05 00:00:00.000 (所影响的行数为 7 行) */--功能:找出在2个日期之间的日期--startdate:2009年9月15日 endDate:2009年10月3日 declare @startdate datetime,@enddate datetimeset @startdate='2009-08-30'set @enddate='2009-09-05'select convert(varchar(10),dateadd(day,number,@startdate),120) from master..spt_values where datediff(day,dateadd(day,number,@startdate), @enddate)>=0 and number>0 and type='p'/*----------2009-08-312009-09-012009-09-022009-09-032009-09-042009-09-05(6 行受影响)/*
[解决办法]
declare @datetime datetimeset @datetime='2012-01-05'select DATEADD(mm, DATEDIFF(mm,0,cast(year(@datetime) as char(4))+'-'+right('0'+cast(number as char(2)),2)+'-01' ), 0) from master..spt_valueswhere type='p' and number>=1 and number<=12union SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,cast(year(@datetime) as char(4))+'-'+right('0'+cast(number as char(2)),2)+'-01')+1, 0))from master..spt_valueswhere type='p' and number>=1 and number<=12