T-SQL 生成一个简易的 公历年历 T-SQL 含日期所在月及
来源:互联网 发布:shadow defender 知乎 编辑:程序博客网 时间:2024/05/21 06:44
--增加了日期所在月及年的周次!
--星期日要算在"上一周"!(注意 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]
,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 datename(weekday,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@),0)))) = '星期日'
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)))
end
,case when datename(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) = '星期日'
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 datename(weekday,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)))))))= '星期日'
then dateadd(day,-1,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)))))))
else 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))))))
end
,case when datename(weekday,dateadd(day,0,datediff(day,0,dateadd(day,d.i,dateadd(month,m.i,dateadd(year,datediff(year,0,@),0)))))) = '星期日'
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 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
into D
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]
select month(min(date))
,(select min(date)
from d
where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期日' and datediff(month,min(a.date),date) = 0) as 星期日
, (select min(date)
from d
where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期一' and datediff(month,min(a.date),date) = 0) as 星期一
,(select min(date)
from d
where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期二' and datediff(month,min(a.date),date) = 0) as 星期二
,(select min(date)
from d
where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期三' and datediff(month,min(a.date),date) = 0) as 星期三
,(select min(date)
from d
where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期四' and datediff(month,min(a.date),date) = 0) as 星期四
,(select min(date)
from d
where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期五' and datediff(month,min(a.date),date) = 0) as 星期五
,(select min(date)
from d
where datepart(week,date) = datepart(week,a.date) and datename(weekday,date)='星期六' and datediff(month,min(a.date),date) = 0) as 星期六
from d a
group by datediff(month,0,date),datepart(week,date)
- T-SQL 生成一个简易的 公历年历 T-SQL 含日期所在月及
- [转]T-SQL 生成一个简易的 公历年历 T-SQL 含日期所在月及年的周次
- T-SQL生成一个简易的公历年历T-SQL含日期所在月及年的周次
- T-SQL 生成 两个新的真正的公历年历
- T-SQL 生成 两个新的真正的公历年历
- T-SQL生成两个新的真正的公历年历
- T-SQL 生成的公历年历 (写在了存储过程中了)
- T-SQL 及SQL
- T-SQL: 取日期
- T-SQL日期相关
- T-SQL 日期转换
- SQL SERVER中,生成若干年份,包含列名公历日期、星期、月干支、日干支的表
- 获取当前日期的T-SQL语句
- 获取当前日期的T-SQL语句
- T-SQL日期处理相关
- T-SQL 日期常用函数
- sql 语句(日期所在月的第一天,最后一天)
- 找含匹配列的行-T-SQL系列
- 转贴自JoyASP:.NET框架(一)
- 几十个实用的PL/SQL(3)
- 深入讲解 ASP+ 验证
- Oracle 10g for Windows 32bit安装图解
- [c#]:如何在C#中读写INI文件(四)
- T-SQL 生成一个简易的 公历年历 T-SQL 含日期所在月及
- 从App到Application (CSDN)
- 手工转移数据库Step By Step
- 一段log execption 的代码
- 伟大的解决方案—DataWindow.Net(一)
- 大数据量下的分页
- 如何在Windows 2000环境中Kill掉单个Oracle线程
- 使用C#拷贝String到struct
- SQL SERVER 2000系统提供的跟踪函数(一)