SQL如何算出一年中有多少周及每周的日期段,设周末为第一天
请教大家:
周别定义 自周日 ~ 周六 例如: 2012年第一周 01/01~01/07天数为 7天,以此类推;
想得出的结果如:
年份 周别 日期范围
2012 1 2012/01/01~2012/01/07
.
.
.
先谢谢了!
[解决办法]
;with ach as( select dateadd(dd,number,cast(ltrim(year(getdate()))+'-01-01' as datetime)) date from master..spt_values where [type] = 'p' and number between 0 and 400),cte as( select * from ach where year(date) = year(getdate()))select datepart(wk,date) as wk,min(date) mindate,max(date) maxdatefrom ctegroup by datepart(wk,date)/********************wk mindate maxdate----------- ----------------------- -----------------------1 2012-01-01 00:00:00.000 2012-01-07 00:00:00.0002 2012-01-08 00:00:00.000 2012-01-14 00:00:00.0003 2012-01-15 00:00:00.000 2012-01-21 00:00:00.0004 2012-01-22 00:00:00.000 2012-01-28 00:00:00.0005 2012-01-29 00:00:00.000 2012-02-04 00:00:00.0006 2012-02-05 00:00:00.000 2012-02-11 00:00:00.0007 2012-02-12 00:00:00.000 2012-02-18 00:00:00.0008 2012-02-19 00:00:00.000 2012-02-25 00:00:00.0009 2012-02-26 00:00:00.000 2012-03-03 00:00:00.00010 2012-03-04 00:00:00.000 2012-03-10 00:00:00.00011 2012-03-11 00:00:00.000 2012-03-17 00:00:00.00012 2012-03-18 00:00:00.000 2012-03-24 00:00:00.00013 2012-03-25 00:00:00.000 2012-03-31 00:00:00.00014 2012-04-01 00:00:00.000 2012-04-07 00:00:00.00015 2012-04-08 00:00:00.000 2012-04-14 00:00:00.00016 2012-04-15 00:00:00.000 2012-04-21 00:00:00.00017 2012-04-22 00:00:00.000 2012-04-28 00:00:00.00018 2012-04-29 00:00:00.000 2012-05-05 00:00:00.00019 2012-05-06 00:00:00.000 2012-05-12 00:00:00.000...
[解决办法]
有函数的，自己拿去改一下
SQL季度函数
SELECT DATEPART(quarter,GETDATE()) --获取当前季度
这个季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
上个季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate())-1, 0)
上上个季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate())-2, 0)
上上上个季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate())-3, 0)
......
n个季度前的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate())-n, 0)
这个季度的最后一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate())+1, -1)
上个季度的最后一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), -1)
上上个季度的最后一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate())-1, -1)
n个季度前的最后一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate())-n+1, -1)
一个月第一天的SQL 脚本：
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)
上个月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
去年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
本月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。
本月的第一个星期一
select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)
[解决办法]
declare @a datetime ='2011-01-01', @b datetime ='2012-01-01'select * from(select '2011' as [年], number/7+1 as [周],convert(varchar(10),dateadd(dd,number/7,@a),120)+'~'+convert(varchar(10),dateadd(dd,number/7+7,@a),120) as [范围]from master..spt_values where type='p' and number<datediff(dd,@a,@b)) tgroup by [年],周,[范围]/*年 周 范围---- ----------- ---------------------2011 1 2011-01-01~2011-01-082011 2 2011-01-02~2011-01-092011 3 2011-01-03~2011-01-102011 4 2011-01-04~2011-01-112011 5 2011-01-05~2011-01-122011 6 2011-01-06~2011-01-132011 7 2011-01-07~2011-01-142011 8 2011-01-08~2011-01-152011 9 2011-01-09~2011-01-162011 10 2011-01-10~2011-01-172011 11 2011-01-11~2011-01-182011 12 2011-01-12~2011-01-192011 13 2011-01-13~2011-01-202011 14 2011-01-14~2011-01-212011 15 2011-01-15~2011-01-222011 16 2011-01-16~2011-01-232011 17 2011-01-17~2011-01-242011 18 2011-01-18~2011-01-252011 19 2011-01-19~2011-01-262011 20 2011-01-20~2011-01-272011 21 2011-01-21~2011-01-282011 22 2011-01-22~2011-01-292011 23 2011-01-23~2011-01-302011 24 2011-01-24~2011-01-312011 25 2011-01-25~2011-02-012011 26 2011-01-26~2011-02-022011 27 2011-01-27~2011-02-032011 28 2011-01-28~2011-02-042011 29 2011-01-29~2011-02-052011 30 2011-01-30~2011-02-062011 31 2011-01-31~2011-02-072011 32 2011-02-01~2011-02-082011 33 2011-02-02~2011-02-092011 34 2011-02-03~2011-02-102011 35 2011-02-04~2011-02-112011 36 2011-02-05~2011-02-122011 37 2011-02-06~2011-02-132011 38 2011-02-07~2011-02-142011 39 2011-02-08~2011-02-152011 40 2011-02-09~2011-02-162011 41 2011-02-10~2011-02-172011 42 2011-02-11~2011-02-182011 43 2011-02-12~2011-02-192011 44 2011-02-13~2011-02-202011 45 2011-02-14~2011-02-212011 46 2011-02-15~2011-02-222011 47 2011-02-16~2011-02-232011 48 2011-02-17~2011-02-242011 49 2011-02-18~2011-02-252011 50 2011-02-19~2011-02-262011 51 2011-02-20~2011-02-272011 52 2011-02-21~2011-02-282011 53 2011-02-22~2011-03-01(53 行受影响)*/
[解决办法]
5楼还是错啊
[解决办法]
declare @d dateselect @d='2012-01-01';with t as(select dateadd(d,number,@d) dfrom master.dbo.spt_valueswhere [type]='P' and number<datediff(d,@d,dateadd(yy,1,@d)))select datepart(yy,d) '年份', datepart(week,d) '周别',convert(varchar,min(d),111)+'~'+convert(varchar,max(d),111) '日期范围'from tgroup by datepart(yy,d),datepart(week,d) 年份 周别 日期范围----------- ----------- -----------------------2012 1 2012/01/01~2012/01/072012 2 2012/01/08~2012/01/142012 3 2012/01/15~2012/01/212012 4 2012/01/22~2012/01/282012 5 2012/01/29~2012/02/042012 6 2012/02/05~2012/02/112012 7 2012/02/12~2012/02/182012 8 2012/02/19~2012/02/252012 9 2012/02/26~2012/03/032012 10 2012/03/04~2012/03/102012 11 2012/03/11~2012/03/172012 12 2012/03/18~2012/03/242012 13 2012/03/25~2012/03/312012 14 2012/04/01~2012/04/072012 15 2012/04/08~2012/04/142012 16 2012/04/15~2012/04/212012 17 2012/04/22~2012/04/282012 18 2012/04/29~2012/05/052012 19 2012/05/06~2012/05/122012 20 2012/05/13~2012/05/192012 21 2012/05/20~2012/05/262012 22 2012/05/27~2012/06/022012 23 2012/06/03~2012/06/092012 24 2012/06/10~2012/06/162012 25 2012/06/17~2012/06/232012 26 2012/06/24~2012/06/302012 27 2012/07/01~2012/07/072012 28 2012/07/08~2012/07/142012 29 2012/07/15~2012/07/212012 30 2012/07/22~2012/07/282012 31 2012/07/29~2012/08/042012 32 2012/08/05~2012/08/112012 33 2012/08/12~2012/08/182012 34 2012/08/19~2012/08/252012 35 2012/08/26~2012/09/012012 36 2012/09/02~2012/09/082012 37 2012/09/09~2012/09/152012 38 2012/09/16~2012/09/222012 39 2012/09/23~2012/09/292012 40 2012/09/30~2012/10/062012 41 2012/10/07~2012/10/132012 42 2012/10/14~2012/10/202012 43 2012/10/21~2012/10/272012 44 2012/10/28~2012/11/032012 45 2012/11/04~2012/11/102012 46 2012/11/11~2012/11/172012 47 2012/11/18~2012/11/242012 48 2012/11/25~2012/12/012012 49 2012/12/02~2012/12/082012 50 2012/12/09~2012/12/152012 51 2012/12/16~2012/12/222012 52 2012/12/23~2012/12/292012 53 2012/12/30~2012/12/31(53 row(s) affected)
[解决办法]
select number+1 as wknum,dateadd(wk,number,'2012-01-01') as firstday,(case when year(dateadd(d,-1,dateadd(wk,number+1,'2012-01-01')))>2012 then '2012-12-31' else dateadd(d,-1,dateadd(wk,number+1,'2012-01-01')) end) as sevendayfrom master..spt_valueswhere type='p' and year(dateadd(wk,number,'2012-01-01'))=2012/*wknum firstday sevenday----------- ----------------------- -----------------------1 2012-01-01 00:00:00.000 2012-01-07 00:00:00.0002 2012-01-08 00:00:00.000 2012-01-14 00:00:00.0003 2012-01-15 00:00:00.000 2012-01-21 00:00:00.0004 2012-01-22 00:00:00.000 2012-01-28 00:00:00.0005 2012-01-29 00:00:00.000 2012-02-04 00:00:00.0006 2012-02-05 00:00:00.000 2012-02-11 00:00:00.0007 2012-02-12 00:00:00.000 2012-02-18 00:00:00.0008 2012-02-19 00:00:00.000 2012-02-25 00:00:00.0009 2012-02-26 00:00:00.000 2012-03-03 00:00:00.00010 2012-03-04 00:00:00.000 2012-03-10 00:00:00.00011 2012-03-11 00:00:00.000 2012-03-17 00:00:00.00012 2012-03-18 00:00:00.000 2012-03-24 00:00:00.00013 2012-03-25 00:00:00.000 2012-03-31 00:00:00.00014 2012-04-01 00:00:00.000 2012-04-07 00:00:00.00015 2012-04-08 00:00:00.000 2012-04-14 00:00:00.00016 2012-04-15 00:00:00.000 2012-04-21 00:00:00.00017 2012-04-22 00:00:00.000 2012-04-28 00:00:00.00018 2012-04-29 00:00:00.000 2012-05-05 00:00:00.00019 2012-05-06 00:00:00.000 2012-05-12 00:00:00.00020 2012-05-13 00:00:00.000 2012-05-19 00:00:00.00021 2012-05-20 00:00:00.000 2012-05-26 00:00:00.00022 2012-05-27 00:00:00.000 2012-06-02 00:00:00.00023 2012-06-03 00:00:00.000 2012-06-09 00:00:00.00024 2012-06-10 00:00:00.000 2012-06-16 00:00:00.00025 2012-06-17 00:00:00.000 2012-06-23 00:00:00.00026 2012-06-24 00:00:00.000 2012-06-30 00:00:00.00027 2012-07-01 00:00:00.000 2012-07-07 00:00:00.00028 2012-07-08 00:00:00.000 2012-07-14 00:00:00.00029 2012-07-15 00:00:00.000 2012-07-21 00:00:00.00030 2012-07-22 00:00:00.000 2012-07-28 00:00:00.00031 2012-07-29 00:00:00.000 2012-08-04 00:00:00.00032 2012-08-05 00:00:00.000 2012-08-11 00:00:00.00033 2012-08-12 00:00:00.000 2012-08-18 00:00:00.00034 2012-08-19 00:00:00.000 2012-08-25 00:00:00.00035 2012-08-26 00:00:00.000 2012-09-01 00:00:00.00036 2012-09-02 00:00:00.000 2012-09-08 00:00:00.00037 2012-09-09 00:00:00.000 2012-09-15 00:00:00.00038 2012-09-16 00:00:00.000 2012-09-22 00:00:00.00039 2012-09-23 00:00:00.000 2012-09-29 00:00:00.00040 2012-09-30 00:00:00.000 2012-10-06 00:00:00.00041 2012-10-07 00:00:00.000 2012-10-13 00:00:00.00042 2012-10-14 00:00:00.000 2012-10-20 00:00:00.00043 2012-10-21 00:00:00.000 2012-10-27 00:00:00.00044 2012-10-28 00:00:00.000 2012-11-03 00:00:00.00045 2012-11-04 00:00:00.000 2012-11-10 00:00:00.00046 2012-11-11 00:00:00.000 2012-11-17 00:00:00.00047 2012-11-18 00:00:00.000 2012-11-24 00:00:00.00048 2012-11-25 00:00:00.000 2012-12-01 00:00:00.00049 2012-12-02 00:00:00.000 2012-12-08 00:00:00.00050 2012-12-09 00:00:00.000 2012-12-15 00:00:00.00051 2012-12-16 00:00:00.000 2012-12-22 00:00:00.00052 2012-12-23 00:00:00.000 2012-12-29 00:00:00.00053 2012-12-30 00:00:00.000 2012-12-31 00:00:00.000(53 行受影响)*/
[解决办法]
--本周第一天 SELECT DATEADD(Day,1-(DATEPART(Weekday,getdate())+@@DATEFIRST-1)%7,getdate()) --or select dateadd(wk, datediff(wk,0,getdate()), 0) --本周第一天 select dateadd(wk, datediff(wk,0,getdate()), 6) --上月第一天 SELECT CONVERT(CHAR(10),DATEADD(month,-1,DATEADD(dd,-DAY(GETDATE())+1,GETDATE())),111) --上月最后一天 SELECT CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()),0)),111)+' 23:59:59' --本月第一天 select dateadd(dd,-datepart(dd,getdate())+1,getdate()) --本月最后一天 select dateadd(dd,-datepart(dd,getdate()) ,dateadd(mm,1,getdate())) --本月天数 select datediff(dd,dateadd(dd,-datepart(dd,getdate())+1,getdate()), dateadd(dd,-datepart(dd,getdate())+1,dateadd(mm,1,getdate()))) --or select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-01' as datetime)))) --下月第一天 select dateadd(dd,-datepart(dd,getdate())+1,dateadd(mm,1,getdate())) --下月最后一天 SELECT CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+2,0)),111)+' 23:59:59' --季度第一天 SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) --季度最后一天（直接推算法） SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1+DATEPART(Quarter,getdate())*3-Month(getdate()),getdate()),120)+'1') --季度的最后一天（CASE判断法） select DATEADD(Month,DATEPART(Quarter,getdate())*3-Month(getdate()),getdate()) --本月第一个星期一 SELECT DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DAY(getdate()), getdate())), '') --去年最后一天 SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) --今年第一天 SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) --今年最后一天 SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1,0)) --指定日期所在周的任意一天 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) ---周内的第几日 select datepart(weekday,getdate()) as 周内的第几日 --年内的第几周 select datepart(week,getdate()) as 年内的第几周 --年内的第几季 select datepart(quarter,getdate()) as 年内的第几季 --判断某天是当月的第几周的sql函数 CREATE FUNCTION WeekOfMonth(@day datetime) RETURNS int AS begin ----declare @day datetime declare @num int declare @Start datetime declare @dd int declare @dayofweek char(8) declare @dayofweek_num char(8) declare @startWeekDays int ---set @day='2009-07-05' if datepart(dd,@day)=1 return 1 else set @Start= (SELECT DATEADD(mm, DATEDIFF(mm,0,@day), 0)) --一个月第一天的 set @dayofweek= (datename(weekday,@Start)) ---得到本月第一天是周几 set @dayofweek_num=(select (case @dayofweek when '星期一' then 2 when '星期二' then 3 when '星期三' then 4 when '星期四' then 5 when '星期五' then 6 when '星期六' then 7 when '星期日' then 1 end)) set @dayofweek_num= 7-@dayofweek_num+1 ---得到本月的第一周一共有几天 ---print @dayofweek_num set @dd=datepart(dd,@day) ----得到今天是这个月的第几天 --print @dd if @dd<=@dayofweek_num --小于前一周的天数 return 1 else set @dd=@dd-@dayofweek_num if @dd % 7=0 begin set @num=@dd / 7 return @num+1 end else --if @dd % 7<>0 set @num=@dd / 7 set @num=@num+1+1 return @num end
[解决办法]
set datefirst 7--设置每周的起始日期为 周末（美国（数据库）默认）
;with ach as
(
select dateadd(dd,number,cast(ltrim(year(getdate()))+'-01-01' as datetime)) date
from master..spt_values
where [type] = 'p' and number between 0 and 400
),cte as
(
select *
from ach
where year(date) = year(getdate())
)
select top 10 datepart(wk,date) as wk,min(date) mindate,max(date) maxdate
from cte
group by datepart(wk,date)
