15 个与日期时间相关的自定义函数-会员卡,广州印刷,深圳印刷,印刷公司,名片,彩页,名片设计,设计教程,名片制作,彩色名片,名片模板,贵宾卡,pvc卡,设计欣赏,深圳名片,广州名片,business card design

首页	名片设计 CorelDRAW Illustrator AuotoCAD Painter 其他软件 Photoshop Fireworks Flash

» 彩色名片
» PVC卡
» 彩色磁性卡
» 彩页/画册
» 个性印务
» 彩色不干胶
» 明信片
   » 明信片
   » 彩色书签
   » 门挂
» 其他产品与服务
   » 创业锦囊
   » 办公用品
   » 信封、信纸
   » 便签纸、斜面纸砖
   » 无碳复印纸
   » 海报
   » 大篇幅印刷
   » KT板
   » 海报
   » 横幅

15 个与日期时间相关的自定义函数
/*T-SQL: 15 个与日期时间相关的自定义函数(UDF),周日作为周的最后一天,均不受 @@DateFirst、语言版本影响都是从老文章里收集或提炼出来的!提示:(@@Datefirst + datepart(weekday,@Date)) % 7 判定周几是最保险的! 与 @@DateFirst 无关,与语言版本无关@@DateFirst 可能会导致 datepart(weekday,@Date) 不相同!无论 @@DateFirst 等于几,无论是什么语言版本的 SQL Server 下面永远恒成立!(@@Datefirst + datepart(weekday,@Date)): 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())beginreturn datediff(year,@StartDate,@EndDate) - case when datediff(day,dateadd(year,datediff(year,@StartDate,@EndDate),@StartDate),@EndDate) >= 0 then 0 else 1 endend

go

create function udf_DaysOfYearByDate(@Date datetime)RETURNS integer-- 返回年的天数可判定平(365)、润(366) 年beginreturn 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) 年beginreturn 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 的十二点asbeginreturn 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)) --下午归到十二点 endend

go

create function udf_WeekDiff(@StartDate datetime,@EndDate datetime)returns integer-- 返回 [@StartDate , @EndDate] 之间周数周日是当周的最后一天beginreturn 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 endend

go

create function udf_WeekOfMonth(@Date datetime)-- 返回 @Date 是所在月的第几周周日是当周的最后一天returns integerasbeginreturn 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 ) + 1end

go

create function udf_WeekOfQuarter(@Date datetime)-- 返回 @Date 是所在季度的第几周周日是当周的最后一天returns intasbeginreturn 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 ) + 1end

go

create function udf_WeekOfYear(@Date datetime)-- 返回 @Date 是所在年的第几周周日是当周的最后一天returns intasbeginreturn 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 ) + 1end

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: Sundayreturn 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 datetimedeclare @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 返回 1begin--1: Monday , ... ,7: Sundayreturn (@@Datefirst + datepart(weekday,@Date)) % 7 + case when (@@Datefirst + datepart(weekday,@Date)) % 7 < 2 then 6 else -1 endend

go

create function udf_NextWorkDate(@Date datetime)returns datetime-- 返回 @Date 的下一个工作日begin/*declare @i intset @i = 3declare @Date datetimeset @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) endend

go

create function udf_PreviousWorkDate(@Date datetime)returns datetime-- 返回 @Date 的上一个工作日begin/*declare @i intset @i = 3declare @Date datetimeset @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) endend

go

create function udf_WorkDateAdd(@i integer,@Date datetime)returns datetime-- 返回 @Date 加上一段 @i 个工作日的新值begindeclare @ intset @ = 0while @ < abs(@i)begin set @Date = case when @i >= 0 then --dbo.udf_nextworkdate(@Date) 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 else --dbo.udf_previousworkdate(@Date) 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 set @ = @ + 1 endreturn @Dateend

go

create function udf_GetStar (@ datetime)RETURNS varchar(100)-- 返回日期所属星座BEGINRETURN(--declare @ datetime--set @ = getdate()select max(star)from(select \\\'魔羯座\\\' as star,1 as [month],1 as [day]union all select \\\'水瓶座\\\',1,20union all select \\\'双鱼座\\\',2,19union all select \\\'牡羊座\\\',3,21union all select \\\'金牛座\\\',4,20union all select \\\'双子座\\\',5,21union all select \\\'巨蟹座\\\',6,22union all select \\\'狮子座\\\',7,23union all select \\\'处女座\\\',8,23union all select \\\'天秤座\\\',9,23union all select \\\'天蝎座\\\',10,24union all select \\\'射手座\\\',11,22union all select \\\'魔羯座\\\',12,22) starswhere 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,20union all select \\\'双鱼座\\\',2,19union all select \\\'牡羊座\\\',3,21union all select \\\'金牛座\\\',4,20union all select \\\'双子座\\\',5,21union all select \\\'巨蟹座\\\',6,22union all select \\\'狮子座\\\',7,23union all select \\\'处女座\\\',8,23union all select \\\'天秤座\\\',9,23union all select \\\'天蝎座\\\',10,24union all select \\\'射手座\\\',11,22union all select \\\'魔羯座\\\',12,22) starswhere @ >= dateadd(month,[month] - 1,dateadd(year,year(@) - year(0),0)) + [day] - 1))end

返回类别: 教程
上一教程: 全面接触SQL语法(2)
下一教程: 如何用批处理对 MYSQL 进行数据操作

您可以阅读与"15 个与日期时间相关的自定义函数"相关的教程:
· 金额阿拉伯数字转变为中文的自定义函数
· 计算指定年月的最后一天的自定义MYSQL函数
· SQL自定义函数的应用
· MYSQL日期和时间函数不求人
· MYSQL的日期和时间函数


	快精灵印艺坊版权所有	首页会员中心在线印刷在线编辑付款方式索取样品设计指南连锁门店网站地图关于我们友情链接