34,590
社区成员
发帖
与我相关
我的任务
分享
DECLARE @dt datetime
SET @dt=GETDATE()
DECLARE @number int
SET @number=3
--1.指定日期该年的第一天或最后一天
--A. 年的第一天
SELECT CONVERT(char(5),@dt,120)+'1-1'
--B. 年的最后一天
SELECT CONVERT(char(5),@dt,120)+'12-31'
--2.指定日期所在季度的第一天或最后一天
--A. 季度的第一天
SELECT CONVERT(datetime,
CONVERT(char(8),
DATEADD(Month,
DATEPART(Quarter,@dt)*3-Month(@dt)-2,
@dt),
120)+'1')
--B. 季度的最后一天(CASE判断法)
SELECT CONVERT(datetime,
CONVERT(char(8),
DATEADD(Month,
DATEPART(Quarter,@dt)*3-Month(@dt),
@dt),
120)
+CASE WHEN DATEPART(Quarter,@dt) in(1,4)
THEN '31'ELSE '30' END)
--C. 季度的最后一天(直接推算法)
SELECT DATEADD(Day,-1,
CONVERT(char(8),
DATEADD(Month,
1+DATEPART(Quarter,@dt)*3-Month(@dt),
@dt),
120)+'1')
--3.指定日期所在月份的第一天或最后一天
--A. 月的第一天
SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1')
--B. 月的最后一天
SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')
--C. 月的最后一天(容易使用的错误方法)
SELECT DATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt))
--4.指定日期所在周的任意一天
SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)
--5.指定日期所在周的任意星期几
--A. 星期天做为一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)
--B. 星期一做为一周的第1天
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)
declare @sDate datetime
declare @eDate datetime
declare @sDate2 datetime
declare @eDate2 datetime
set @sDate='2011-09-01'
set @eDate='2011-09-30'
set @sDate2=dateadd(day,-7,@sDate)
set @eDate2=dateadd(day,7,@eDate)
declare @TA table(weeknum int,[date] datetime)
insert @TA
select datepart(week,dateadd(day,number,@sDate2))+(case when datepart(weekday,dateadd(day,number,@sDate2))=1 then -1 else 0 end) as weeknum,
dateadd(day,number,@sDate2) as [date]
from master..spt_values
where type='p' and datediff(day,dateadd(day,number,@sDate2),@eDate2)>=0
select weeknum,min([date]),max([date]) from @TA
where weeknum>=datepart(week,@sDate)+(case when datepart(weekday,@sDate)=1 then -1 else 0 end)
and weeknum<=datepart(week,@eDate)+(case when datepart(weekday,@eDate)=1 then -1 else 0 end)
group by weeknum
weeknum
----------- ------------------------------------------------------ ------------------------------------------------------
36 2011-08-29 00:00:00.000 2011-09-04 00:00:00.000
37 2011-09-05 00:00:00.000 2011-09-11 00:00:00.000
38 2011-09-12 00:00:00.000 2011-09-18 00:00:00.000
39 2011-09-19 00:00:00.000 2011-09-25 00:00:00.000
40 2011-09-26 00:00:00.000 2011-10-02 00:00:00.000
declare @dt1 datetime,@dt2 datetime
set @dt1='2011-09-01'
set @dt2='2011-09-30'
;with c1 as(
select dateadd(d,number,dateadd(d,-datepart(dw,@dt1)+2,@dt1))dt from master..spt_values where type='p' and dateadd(d,number,dateadd(d,-datepart(dw,@dt1)+2,@dt1))<=dateadd(d,8-datepart(dw,@dt2),@dt2)
),c2 as(
select datepart(wk,dateadd(d,-1,dt))-datepart(wk,@dt1)+1 wk,dt from c1
)
select wk 周数,min(dt) 开始日期,max(dt)结束日期 from c2 group by wk
/*
周数 开始日期 结束日期
----------- ----------------------- -----------------------
1 2011-08-29 00:00:00.000 2011-09-04 00:00:00.000
2 2011-09-05 00:00:00.000 2011-09-11 00:00:00.000
3 2011-09-12 00:00:00.000 2011-09-18 00:00:00.000
4 2011-09-19 00:00:00.000 2011-09-25 00:00:00.000
5 2011-09-26 00:00:00.000 2011-10-02 00:00:00.000
(5 行受影响)
*/
create function getwkday
(@dt1 datetime,@dt2 datetime)
returns @t table(周数 int,开始日期 datetime,结束日期 datetime)
begin
declare @dt datetime,@n int
set @dt=dateadd(d,-datepart(dw,@dt1)+2,@dt1)
set @n=1
while @dt<@dt2
begin
insert into @t select @n,@dt,dateadd(d,6,@dt)
set @dt=dateadd(d,7,@dt)
set @n=@n+1
end
return
end
go
select * from dbo.getwkday('2011-09-01','2011-09-30')
/*
周数 开始日期 结束日期
----------- ----------------------- -----------------------
1 2011-08-29 00:00:00.000 2011-09-04 00:00:00.000
2 2011-09-05 00:00:00.000 2011-09-11 00:00:00.000
3 2011-09-12 00:00:00.000 2011-09-18 00:00:00.000
4 2011-09-19 00:00:00.000 2011-09-25 00:00:00.000
5 2011-09-26 00:00:00.000 2011-10-02 00:00:00.000
(5 行受影响)
*/
select * from dbo.getwkday('2011-07-01','2011-07-31')
/*
周数 开始日期 结束日期
----------- ----------------------- -----------------------
1 2011-06-27 00:00:00.000 2011-07-03 00:00:00.000
2 2011-07-04 00:00:00.000 2011-07-10 00:00:00.000
3 2011-07-11 00:00:00.000 2011-07-17 00:00:00.000
4 2011-07-18 00:00:00.000 2011-07-24 00:00:00.000
5 2011-07-25 00:00:00.000 2011-07-31 00:00:00.000
(5 行受影响)
*/
go
drop function dbo.getwkday
用函数解决。(libin_ftsafe)
create function f_calendar(@year int,@month int)
returns @t table(日 varchar(4),一 varchar(4),二 varchar(4),三 varchar(4),四 varchar(4),五 varchar(4),六 varchar(4))
as
begin
declare @a table(id int identity(0,1),date datetime)
insert into @a(date)
select top 31 rtrim(@year)+'-'+rtrim(@month)+'-1' from sysobjects
update @a set date=dateadd(dd,id,date)
insert into @t
select
max(case datepart(dw,date) when 7 then rtrim(day(date)) else '' end),
max(case datepart(dw,date) when 1 then rtrim(day(date)) else '' end),
max(case datepart(dw,date) when 2 then rtrim(day(date)) else '' end),
max(case datepart(dw,date) when 3 then rtrim(day(date)) else '' end),
max(case datepart(dw,date) when 4 then rtrim(day(date)) else '' end),
max(case datepart(dw,date) when 5 then rtrim(day(date)) else '' end),
max(case datepart(dw,date) when 6 then rtrim(day(date)) else '' end)
from
@a
where
month(date)=@month
group by
(case datepart(dw,date) when 7 then datepart(week,date)+1 else datepart(week,date) end)
return
end
go
set datefirst 1
select * from dbo.f_calendar(2007,12)
/*
日 一 二 三 四 五 六
---- ---- ---- ---- ---- ---- ----
1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31
*/
go
drop function f_calendar
go
declare @dt1 datetime,@dt2 datetime
set @dt1='2011-09-01'
set @dt2='2011-09-30'
;with c1 as(
select dateadd(d,number,dateadd(d,-datepart(dw,@dt1)+2,@dt1))dt from master..spt_values where type='p' and dateadd(d,number,dateadd(d,-datepart(dw,@dt1)+2,@dt1))<=dateadd(d,8-datepart(dw,@dt2),@dt2)
),c2 as(
select datepart(wk,dt)-datepart(wk,@dt1)+1 wk,dt from c1
)select wk 周数,min(dt) 开始日期,max(dt)结束日期 from c2 group by wk
/*
周数 开始日期 结束日期
----------- ----------------------- -----------------------
1 2011-08-29 00:00:00.000 2011-09-03 00:00:00.000
2 2011-09-04 00:00:00.000 2011-09-10 00:00:00.000
3 2011-09-11 00:00:00.000 2011-09-17 00:00:00.000
4 2011-09-18 00:00:00.000 2011-09-24 00:00:00.000
5 2011-09-25 00:00:00.000 2011-10-01 00:00:00.000
6 2011-10-02 00:00:00.000 2011-10-02 00:00:00.000
(6 行受影响)
*/
--关键字
set datefirst 1
datepart(week,日期)
datepart(weekday,日期)
--自己分析处理下