T-SQL 生成一个简易的 公历年历 T-SQL 含日期所在月及年的周次

原创 2004年04月05日 23:13:00

--增加了日期所在月及年的周次!
--星期日要算在"上一周"!(注意 WeekOfYear、WeekOfMonth 与 MyWeekOfYear、MyWeekOfMonth 的区别)
--注意 datename 的值会因 SQL Server 语言版本或日期格式有所差异!
--本测试环境为: SQL Server 2000 简体中文版 + Windows 简体中文版


declare @ datetime
set @ = '1995-02-25 11:00:50' -- 1995-01-01 正好是个星期日

select @ as 日期
      ,dateadd(year,datediff(year,0,@),0) as 所在年的第一天
      ,dateadd(year,1+datediff(year,0,@),0)-1 as 所在年的最后一天
      ,dateadd(quarter,datediff(quarter,0,@),0) as 所在季的第一天
      ,dateadd(quarter,1+datediff(quarter,0,@),0)-1 as 所在季的最后一天
      ,dateadd(month,datediff(month,0,@),0) as 所在月的第一天
      ,dateadd(month,1+datediff(month,0,@),0)-1 as 所在月的最后一天
      ,dateadd(week,datediff(week,0,@),0) as 所在周的第一天
      ,dateadd(week,1+datediff(week,0,@),0)-1 as 所在周的最后一天


select
 dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))) as [Date]
,datename(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) as [WeekDayName]
,datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) as [WeekDay]
,(@@datefirst + datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))) % 7 as [MyWeekDay]

,datepart(week,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) as WeekOfYear
,datediff(week
                 ,case when (@@datefirst + datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@),0))))) % 7 = 1
                            then dateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@),0))))
                       else dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@),0))) --date 所在年的第一天 即: 一月一号
                  end
                 ,case when (@@datefirst + datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))) % 7 = 1
                            then dateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))
                       else dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))
                  end
                ) + 1 as MyWeekOfYear


,datediff(week,dateadd(day,1-day(dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) +1 as WeekOfMonth

,datediff(week
                 ,case when (@@datefirst + datepart(weekday,dateadd(month,datediff(month,0,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),0))) % 7 = 1
                            then dateadd(month,datediff(month,0,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),0) -1
                       else dateadd(month,datediff(month,0,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))),0)
                  end
                 ,case when (@@datefirst + datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0))))))) % 7 = 1
                            then dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))-1
                       else dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))
                  end
                ) + 1 as MyWeekOfMonth

,datepart(dayofyear,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) as DayOfYear

from
(
select 0 as i
union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6
union all select 7 union all select 8 union all select 9
union all select 10 union all select 11
) M
,
(
select 0 as i
union all select 1 union all select 2 union all select 3 union all select 4 union all select 5
union all select 6 union all select 7 union all select 8 union all select 9 union all select 10
union all select 11 union all select 12 union all select 13 union all select 14
union all select 15 union all select 16 union all select 17 union all select 18
union all select 19 union all select 20 union all select 21 union all select 22
union all select 23 union all select 24 union all select 25 union all select 26
union all select 27 union all select 28 union all select 29 union all select 30
) d

where datediff(month,dateadd(year,datediff(year,0,@),0),dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))) = m.i
order by [Date]

[转]T-SQL 生成一个简易的 公历年历 T-SQL 含日期所在月及年的周次

--增加了日期所在月及年的周次!--星期日要算在"上一周"!(注意 WeekOfYear、WeekOfMonth 与 MyWeekOfYear、MyWeekOfMonth 的区别)--注意 daten...
  • hychieftain
  • hychieftain
  • 2004年11月19日 10:41
  • 995

T-SQL生成一个简易的公历年历T-SQL含日期所在月及年的周次

  • zgqtxwd
  • zgqtxwd
  • 2008年04月27日 07:12
  • 139

T-SQL 生成一个简易的 公历年历 T-SQL 含日期所在月及

  • zgqtxwd
  • zgqtxwd
  • 2008年05月01日 02:29
  • 81

java-计算任意日期所在周、月、年的第一天与最后一天

/** * 该模块可实现自动计算当前日期对应的周,月,年的第一天与最后一天日期,并且可直接指定当前日期为任意日期。 * * @author Administrator * */public class...
  • k769444252
  • k769444252
  • 2015年06月23日 14:31
  • 2392

【原创】SQL 返回时间所在月份周次,该周起始结束日期

IF EXISTS ( SELECT 1 FROM sysobjects WHERE name = 'fn_Week)     DROP FUNCTION fn_Week GO set A...
  • ljsososo
  • ljsososo
  • 2013年12月09日 15:42
  • 3060

android 计算所在周所在月的前后日期

效果图: 使用了 一个时间相关的工具类   package com.yqy.yqy_date; import android.util.Log; import java.text.Da...
  • u012885461
  • u012885461
  • 2015年06月11日 15:28
  • 1386

根据年份获取周次,根据周次获取日期范围,获取当前年份,周次列表信息。

在原文的基础上改的,适应我这边的需求(姑且算个原创呗,原文链接在最下边)。 (1)根据年份获取周次 (2) 获取当前年份周次 (3) 根据选择周次获取日期时间范围...
  • xingfuzhijianxia
  • xingfuzhijianxia
  • 2014年08月05日 15:44
  • 5569

T-SQL将datetime类型转换为字符型

T-SQL将datetime类型转换为字符型 select   convert(char(8),getdate(),112) 20030319 select   convert(char(10),ge...
  • lijian260
  • lijian260
  • 2011年06月17日 11:47
  • 755

JS版-简易年历

我的简易年历   *{margin:0;padding: 0;} li{list-style: none;} #wrap{width: 210px;margin: 50px auto 0;b...
  • liujie19901217
  • liujie19901217
  • 2015年12月06日 15:34
  • 628

T-SQL 生成 两个新的真正的公历年历

转自:http://dev.csdn.net/develop/article/26/26447.shtm--两个新年历--增加了日期所在月及年的周次!--星期日要算在"上一周"!(注意 WeekOfY...
  • zhangzs8896
  • zhangzs8896
  • 2004年09月21日 10:57
  • 528
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:T-SQL 生成一个简易的 公历年历 T-SQL 含日期所在月及年的周次
举报原因:
原因补充:

(最多只允许输入30个字)