AI 助理
备案控制台
开发者社区 开发与运维 文章 正文

T-SQL: 17 个与日期时间相关的自定义函数(UDF),周日作为周的最后一天,均不受 @@DateFirst、语言版本影响!

2014-12-06 1080
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 原文:T-SQL: 17 个与日期时间相关的自定义函数(UDF),周日作为周的最后一天,均不受 @@DateFirst、语言版本影响!CSDN 的 Blog 太滥了!无时不刻地在坏! 开始抢救性搬家 .
原文: T-SQL: 17 个与日期时间相关的自定义函数(UDF),周日作为周的最后一天,均不受 @@DateFirst、语言版本影响!

CSDN 的 Blog 太滥了!无时不刻地在坏!
开始抢救性搬家 ... ... 到这里重建家园
/*
T-SQL: 17 个与日期时间相关的自定义函数(UDF),周日作为周的最后一天,均不受 @@DateFirst、语言版本影响
都是从老文章里收集或提炼出来的!
提示:
(@@Datefirst + datepart(weekday,@Date)) % 7 判断周几是最保险的! 与 @@DateFirst 无关,与语言版本无关
@@DateFirst 可能会导致 datepart(weekday,@Date) 不一样!
无论 @@DateFirst 等于几,无论是什么语言版本的 SQL Server 下面永远恒成立!
(@@Datefirst + datepart(weekday,@Date))%7 : 2、3、4、5、6、0、1 分别代表 周一 到 周日
-- */

create function udf_GetAge(@StartDate datetime,@EndDate datetime)
returns integer
-- 返回精确年龄 select dbo.udf_GetAge('1949-10-01',getdate())
begin
return datediff(year,@StartDate,@EndDate)
       - case when datediff(day,dateadd(year,datediff(year,@StartDate,@EndDate),@StartDate),@EndDate) >= 0
                   then 0
              else
                   1
         end
end

go

create function udf_DaysOfYearByDate(@Date datetime)
returns integer
-- 返回年的天数 可判断 平(365)、润(366) 年
begin
return datediff(day,dateadd(year,datediff(year,0,@Date),0),dateadd(year,datediff(year,0,@Date) + 1,0))
end

go

create function udf_DaysOfYear(@Year integer)
returns integer
-- 返回年的天数 可判断 平(365)、润(366) 年
begin
return datediff(day,dateadd(year,@year - year(0),0),dateadd(year,@year - year(0) + 1,0))
end

go

create function udf_HalfDay(@Date datetime)
returns datetime
-- 返回 @Date 是 上午 返回 @Date 的零点,@Date 是 下午 返回 @Date 的十二点
begin
return case when datepart(hour,@Date) < 12
                 then dateadd(day,datediff(day,0,@Date),0) --上午归到 零点
            else
                 dateadd(hour,12,dateadd(day,datediff(day,0,@Date),0)) --下午归到 十二点
       end
end

go

create function udf_WeekDiff(@StartDate datetime,@EndDate datetime)
returns integer
-- 返回 [@StartDate , @EndDate] 之间周数 周日是当周的最后一天
begin
return datediff(week,@StartDate,@EndDate) -- + 1
       + case when (@@Datefirst + datepart(weekday,@StartDate)) % 7 = 1
                   then 1
              else
                   0
         end
       - case when (@@Datefirst + datepart(weekday,@EndDate)) % 7 = 1
                   then 1
              else 0
         end
end

go

create function udf_WeekOfMonth(@Date datetime)
-- 返回 @Date 是所在月的第几周 周日是当周的最后一天
returns integer
begin
return datediff(week
                ,case when (@@Datefirst + datepart(weekday,dateadd(month,datediff(month,0,@Date),0))) % 7 = 1
                           then dateadd(month,datediff(month,0,@Date),0) - 1
                      else
                           dateadd(month,datediff(month,0,@Date),0)
                      end
                ,case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1
                           then @Date-1
                      else @Date
                 end
               ) + 1
end

go

create function udf_WeekOfQuarter(@Date datetime)
-- 返回 @Date 是所在季度的第几周 周日是当周的最后一天
returns int
begin
return datediff(week
                ,case when (@@Datefirst + datepart(weekday,dateadd(Quarter,datediff(Quarter,0,@Date),0))) % 7 = 1
                           then dateadd(Quarter,datediff(Quarter,0,@Date),0) - 1
                      else
                           dateadd(Quarter,datediff(Quarter,0,@Date),0)
                 end
                ,case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1
                           then @Date - 1
                      else
                           @Date
                 end
               ) + 1
end

go

create function udf_WeekOfYear(@Date datetime)
-- 返回 @Date 是所在年的第几周 周日是当周的最后一天
returns int
begin
return datediff(week
                ,case when (@@Datefirst + datepart(weekday,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@Date),0))))) % 7 = 1
                           then dateadd(day,-1,dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@Date),0))))
                      else
                           dateadd(day,0,datediff(day,0,dateadd(year,datediff(year,0,@Date),0))) --date 所在年的第一天 即: 一月一号
                 end
                ,case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1
                           then dateadd(day,-1,@Date)
                      else
                           @Date
                 end
               ) + 1
end

go

create function udf_WeekDay(@ int,@Date datetime)
returns datetime
-- 返回 @Date 所在周的其他天 周一 到 周日 也就是映射到 所在周的其他天 周日是当周的最后一天
begin
/*
--周日算作(上一)周的最后一天
 当 @ <= 1 代表将 @Date 映射到 所在周的星期一
 当 @ = 2 代表将 @Date 映射到 所在周的星期二
 当 @ = 3 代表将 @Date 映射到 所在周的星期三
 当 @ = 4 代表将 @Date 映射到 所在周的星期四
 当 @ = 5 代表将 @Date 映射到 所在周的星期五
 当 @ = 6 代表将 @Date 映射到 所在周的星期六
 当 @ >= 7 代表将 @Date 映射到 所在周的星期日
 可用于按周汇总 Group by,均支持跨年跨月数据
*/
return dateadd(day
               ,case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 0 --周六
                          then case when @ between 1 and 6
                                         then @ - 6
                                    else
                                         1
                               end
                     when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1 --周日(七)
                          then case when @ between 1 and 6
                                         then @ - 7
                                    else
                                         0
                               end
                     when (@@Datefirst + datepart(weekday,@Date)) % 7 between 2 and 6 --周一至周五
                          then case when @ between 1 and 6
                                         then @ + 1 - (@@Datefirst + datepart(weekday,@Date)) % 7
                                    else
                                         8 - (@@Datefirst + datepart(weekday,@Date)) % 7
                               end
                end
               ,@Date)
end

go

create function udf_WeekdayDiff(@Weekday integer,@StartDate datetime,@EndDate datetime)
returns integer
-- 返回 [@StartDate , @EndDate] 之间周一 到 周日的个数 周日是当周的最后一天
begin
-- @Weekday: 1: Monday , ... ,7: Sunday
return datediff(week,@StartDate,@EndDate)
       + case when (@@Datefirst + datepart(weekday,@StartDate)) % 7
                   + case when (@@Datefirst + datepart(weekday,@StartDate)) % 7 = 0
                               then 7
                          else
                               0
                     end > @Weekday % 7 + 1
                   then 0
              else 1
         end
       - case when (@@Datefirst + datepart(weekday,@EndDate)) % 7
                   + case when (@@Datefirst + datepart(weekday,@EndDate)) % 7 = 0
                               then 7
                          else 0
                     end >= @Weekday % 7 + 1
                   then
                        0
              else
                   1
         end
/* test:

declare @b datetime
declare @e datetime

set @b = '2004-01-29'
set @e = '2004-09-05'

select @b as BeginDate ,@e as EndDate
,dbo.udf_WeekdayDiff(1,@b,@e) as CountOfMonday
,dbo.udf_WeekdayDiff(2,@b,@e) as CountOfTuesday
,dbo.udf_WeekdayDiff(3,@b,@e) as CountOfWednesday
,dbo.udf_WeekdayDiff(4,@b,@e) as CountOfThursday
,dbo.udf_WeekdayDiff(5,@b,@e) as CountOfFriday
,dbo.udf_WeekdayDiff(6,@b,@e) as CountOfSaturday
,dbo.udf_WeekdayDiff(7,@b,@e) as CountOfSunday
*/
end

go

create function udf_WeekdayID(@Date datetime)
returns integer
-- 返回 @Date 是 Monday 返回 1, ... ,是 Sunday 返回 1
begin
--1: Monday , ... ,7: Sunday
return (@@Datefirst + datepart(weekday,@Date)) % 7
       + case when (@@Datefirst + datepart(weekday,@Date)) % 7 < 2
                   then 6
              else
                   -1
         end
end

go

create function udf_DayOfQuarter(@Date datetime)
-- 返回 @Date 是所在季度的第几天
returns integer
as
begin
/*
declare @date datetime
set @date = '2004-4-1'
--*/
return datediff(day
               ,dateadd(Quarter,datediff(Quarter,0,@Date),0)
               ,@Date
               ) + 1
end

go

create function udf_DaysOfQuarterByDate(@Date datetime)
-- 返回 @Date 所在季度的天数
returns integer
begin
/*
declare @date datetime
set @date = '2004-4-1'
--*/
return datediff(day
               ,dateadd(Quarter,datediff(Quarter,0,@Date),0)
               ,dateadd(Quarter,datediff(Quarter,0,@Date) + 1,0)
               )
end

go

create function udf_NextWorkDate(@Date datetime)
returns datetime
-- 返回 @Date 的下一个工作日
begin
/*
declare @i int
set @i = 3
declare @Date datetime
set @Date = '2005-01-02'
-- */
return case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 6 -- Friday
                 then dateadd(day,3,@Date)
            when (@@Datefirst + datepart(weekday,@Date)) % 7 = 0 -- saturday
                 then dateadd(day,2,@Date)
            else
                 dateadd(day,1,@Date)
       end
end

go

create function udf_PreviousWorkDate(@Date datetime)
returns datetime
-- 返回 @Date 的上一个工作日
begin
/*
declare @i int
set @i = 3
declare @Date datetime
set @Date = '2005-01-02'
-- */
return case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 2 -- Monday
                 then dateadd(day,-3,@Date)
            when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1 -- Sunday
                 then dateadd(day,-2,@Date)
            else
                 dateadd(day,-1,@Date)
       end
end

go

create function udf_WorkDateAdd(@i integer,@Date datetime)
returns datetime
-- 返回 @Date 加上一段 @i 个工作日的新值
begin
declare @ int
set @ = 0
while @ < abs(@i)
begin
   set @Date = case when @i >= 0
                         then --dbo.udf_nextworkdate(@Date)
                              case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 6 -- Friday
                                        then @date + 3 --dateadd(day,3,@Date)
                                   when (@@Datefirst + datepart(weekday,@Date)) % 7 = 0 -- saturday
                                        then  @date + 2 --dateadd(day,2,@Date)
                                   else
                                         @date + 1 --dateadd(day,1,@Date)
                              end
                    else
                         --dbo.udf_previousworkdate(@Date)
                         case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 2 -- Monday
                                   then  @date - 3 --dateadd(day,-3,@Date)
                              when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1 -- Sunday
                                   then  @date - 2  --dateadd(day,-2,@Date)
                              else
                                    @date - 1 --dateadd(day,-1,@Date)
                         end
               end
               set @ = @ + 1
end
return @Date
end

go

create function udf_GetStar (@ datetime)
returns varchar(100)
-- 返回日期所属星座,如果有静态的 星座对照码表 直接在查询中 join 效率相对更高
begin
return
(
--declare @ datetime
--set @ = getdate()
select max(star)
from
(
select '魔羯座' as star,1 as [month],1 as [day]
union all select '水瓶座',1,20
union all select '双鱼座',2,19
union all select '牡羊座',3,21
union all select '金牛座',4,20
union all select '双子座',5,21
union all select '巨蟹座',6,22
union all select '狮子座',7,23
union all select '处女座',8,23
union all select '天秤座',9,23
union all select '天蝎座',10,24
union all select '射手座',11,22
union all select '魔羯座',12,22
) stars
where dateadd(month,[month] - 1,dateadd(year,year(@) - year(0),0)) + [day] - 1 =
(
select max(dateadd(month,[month] - 1,dateadd(year,year(@) - year(0),0)) + [day] - 1)
from (
select '魔羯座' as star,1 as [month],1 as [day]
union all select '水瓶座',1,20
union all select '双鱼座',2,19
union all select '牡羊座',3,21
union all select '金牛座',4,20
union all select '双子座',5,21
union all select '巨蟹座',6,22
union all select '狮子座',7,23
union all select '处女座',8,23
union all select '天秤座',9,23
union all select '天蝎座',10,24
union all select '射手座',11,22
union all select '魔羯座',12,22
) stars
where @ >= dateadd(month,[month] - 1,dateadd(year,year(@) - year(0),0)) + [day] - 1
)
)
end
go
-- 注意这里用 40 足够了,因为每个月至多才 31 天
select a.birthdate,b.star
from employees a
left join
(
select a.*,isnull(b.month,12) as m,isnull(b.day,31) as d
from stars a
left join stars b
on a.month * 40 + a.day < b.month * 40 + b.day
and b.month * 40 + b.day  = (select min(month * 40 + day) from stars where month * 40 + day > a.month * 40 + a.day)
) b
on month(a.birthdate) * 40 + day(a.birthdate) >= b.month * 40 +  b.day
and month(a.birthdate) * 40 + day(a.birthdate) < b.m * 40 +  b.d


select e.birthdate,a.star
from employees e
left join stars a
on month(e.birthdate) * 40 + day(e.birthdate) >= a.month * 40 + a.day
left join stars b
on a.month * 40 + a.day < b.month * 40 + b.day
and b.month * 40 + b.day  = (select min(month * 40 + day) from stars where month * 40 + day > a.month * 40 + a.day)
where month(e.birthdate) * 40 + day(e.birthdate) < isnull(b.month * 40 + b.day,999)

select *
from stars a
left join stars b
on a.month * 40 + a.day < b.month * 40 + b.day
and b.month * 40 + b.day  = (select min(month * 40 + day) from stars where month * 40 + day > a.month * 40 + a.day)
go
--周历
declare @ datetime
set @ = getdate()

select @ +
case when (@@Datefirst + datepart(weekday,@)) % 7 = 0 --周六
          then -5
     when (@@Datefirst + datepart(weekday,@)) % 7 = 1 --周日(七)
          then -6
     when (@@Datefirst + datepart(weekday,@)) % 7 between 2 and 6 --周一至周五
          then 2 - (@@Datefirst + datepart(weekday,@)) % 7
end + N.i
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
) N

//csc noname1.cs
// 装配脑袋 作 C# 计算任意时段内的 之间周一 到 周日的个数 周日是当周的最后一天
using System;
public class Class1
{
 static void Main(string[] args)
 {
  System.Console.WriteLine("Hello World");
  System.DateTime Start = System.DateTime.Parse("2005-3-6");
  System.DateTime End = System.DateTime.Parse("2005-3-7");
  System.Console.WriteLine(WeekdaysIn(DayOfWeek.Monday,Start,End)); //周一
  System.Console.WriteLine(WeekdaysIn(DayOfWeek.Tuesday,Start,End));
  System.Console.WriteLine(WeekdaysIn(DayOfWeek.Wednesday,Start,End));
  System.Console.WriteLine(WeekdaysIn(DayOfWeek.Thursday,Start,End));
  System.Console.WriteLine(WeekdaysIn(DayOfWeek.Friday,Start,End));
  System.Console.WriteLine(WeekdaysIn(DayOfWeek.Saturday,Start,End));
  System.Console.WriteLine(WeekdaysIn(DayOfWeek.Sunday,Start,End)); //周七(日)
  System.Console.ReadLine();
 }
 private static int WeekdaysIn(DayOfWeek Weekday, DateTime StartDate, DateTime EndDate)
 {
  int d = (EndDate - StartDate).Days;
  int w = d / 7;
  int offset = (int) StartDate.DayOfWeek + d % 7;
  if ((int) Weekday < (int) StartDate.DayOfWeek)
  {
   Weekday = (DayOfWeek)((int) Weekday + 7);
  }
  if ((int)Weekday <= offset)
  {
   w ++;
  }
  return w;
 }
}

文章标签：
Go
关键词：
SQL版本
SQL日期
SQL语言
SQL自定义函数
SQL udf
杰克.陈
目录
相关文章
小周sir
|
2月前
|
SQL 关系型数据库 MySQL
在MySQL中,什么是结构化查询语言 (SQL)
【8月更文挑战第20天】在MySQL中,什么是结构化查询语言 (SQL)
小周sir
44 1
源码星辰
|
3月前
|
SQL 存储 关系型数据库
SQL语言优缺点有哪些？
SQL（Structured Query Language）语言作为数据库管理和操作的标准语言，具有一系列的优点，同时也存在一些缺点。
源码星辰
61 7
1100237741946300
|
1天前
|
SQL Oracle 关系型数据库
SQL数据库当前版本概览与更新趋势
在探讨SQL（Structured Query Language）数据库的当前版本时，我们首先要明确的是，SQL本身是一种查询语言标准，而并非特指某一个具体的数据库产品
1100237741946300
10 2
diygwcom
|
3天前
|
SQL 开发框架 .NET
sql server日期时间函数
sql server日期时间函数
diygwcom
14 2
sunrr
|
1月前
|
SQL 数据管理 BI
SQL 有哪些版本？
SQL 有哪些版本？
sunrr
130 4
sunrr
|
1月前
|
SQL 数据管理 BI
SQL Server 有哪些版本？
SQL Server 有哪些版本？
sunrr
100 3
wljslmz
|
2月前
|
SQL 关系型数据库 MySQL
MySQL 中的 SQL：结构化查询语言的全面解析
【8月更文挑战第31天】
wljslmz
77 0
土木林森
|
2月前
|
Java 应用服务中间件 Maven
从零到英雄：一步步构建你的首个 JSF 应用程序，揭开 JavaServer Faces 的神秘面纱
【8月更文挑战第31天】JavaServer Faces (JSF) 是一种强大的 Java EE 标准，用于构建企业级 Web 应用。它提供了丰富的组件库和声明式页面描述语言 Facelets，便于开发者快速开发功能完善且易于维护的 Web 应用。本文将指导你从零开始构建一个简单的 JSF 应用，包括环境搭建、依赖配置、Managed Bean 编写及 Facelets 页面设计。
土木林森
74 0
土木林森
|
2月前
|
SQL 关系型数据库 MySQL
【超全整理】SQL日期与时间函数大汇总会：MySQL与SQL Server双轨对比教学，助你轻松搞定时间数据处理难题！
【8月更文挑战第31天】本文介绍了在不同SQL数据库系统（如MySQL、SQL Server、Oracle）中常用的日期与时间函数，包括DATE、NOW()、EXTRACT()、DATE_ADD()、TIMESTAMPDIFF()及日期格式化等，并提供了具体示例。通过对比这些函数在各系统中的使用方法，帮助开发者更高效地处理日期时间数据，满足多种应用场景需求。
土木林森
99 0
土木林森
|
2月前
|
SQL 存储 大数据
SQL 语言发展史简直太震撼啦！从诞生到现代数据处理，见证一场奇妙的演变之旅，快来感受！
【8月更文挑战第31天】SQL（结构化查询语言）自20世纪70年代由IBM研究员E.F. Codd提出以来，已成为现代数据处理不可或缺的一部分。它最初简化了层次和网状模型中复杂的存储与检索问题，通过基本的SELECT、FROM和WHERE关键字实现了数据查询。80年代，SQL在商业数据库中广泛应用，引入了GROUP BY、HAVING和ORDER BY等功能，增强了数据分析能力。90年代，互联网和企业信息化推动了SQL的进一步优化与扩展，支持分布式数据库和数据仓库等技术。
土木林森
34 0

热门文章

最新文章

  • 1
    RDS for SQL server 空间问题排查汇总
  • 2
    MS SQL 错误 ：17883，严重度: 1，状态: 0
  • 3
    SQL FOR XML
  • 4
    人人都是 DBA（VIII）SQL Server 页存储结构
  • 5
    【巡检问题分析与最佳实践】RDS SQL Server 磁盘IO吞吐高问题
  • 6
    SQL SERVER 临时表的排序问题
  • 7
    PostgreSQL在何处处理 sql查询之三十七
  • 8
    常用SQL语句详解
  • 9
    SQL Server--获取磁盘空间使用情况
  • 10
    SQL SERVER 2008的SQLCMD模式
  • 1
    【MySQL技术之旅】（7）总结和盘点优化方案系列之常用SQL的优化
    102
  • 2
    Cause: java.sql.SQLIntegrityConstraintViolationException: Column ‘id‘ in field list is ambiguous
    65
  • 3
    Auditing SQL Statements, Privileges, and Other General Activities
    31
  • 4
    SQL，Group By 真扎心，原来是这样
    62
  • 5
    踏踏实实练习SQL--day09
    71
  • 6
    Microsoft SQL Server 编写汉字转拼音函数
    118
  • 7
    PL/SQL经典练习
    40
  • 8
    PL/SQL编程基本概念
    48
  • 9
    sql server 2012远程链接的方法及步骤
    71
  • 10
    Seata常见问题之服务端 error日志没有输出,客户端执行sql报错如何解决
    364

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server在电子商务中的应用与实践
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • SQL的增删改查及函数应用
  • PolarDB MySQL自动SQL限流
  • 如何开通SQL Server的专属集群
  • 使用DAS实现数据库自动SQL优化
  • 使用DAS实现数据库自动SQL限流
  • 一小时快速掌握 SQL 语法
    • 下一篇
    阿里云对象存储OSS怎么收费？包年包月和按量付费价格表