/*
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())
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 的十二點
as
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
as
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
as
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
as
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_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 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
end
return @Date
end

go

create function udf_GetStar (@ datetime)
RETURNS varchar(100)
-- 返回日期所屬星座
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