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

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

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

createfunctionUDF_GetAge(@StartDatedatetime,@EndDatedatetime)
returnsinteger
--返回精确年龄selectdbo.UDF_GetAge('1949-10-01',getdate())
begin
returndatediff(year,@StartDate,@EndDate)
      -casewhendatediff(day,dateadd(year,datediff(year,@StartDate,@EndDate),@StartDate),@EndDate)>=0
                  then0
             else
                  1
        end
end

go

createfunctionUDF_DaysOfYearByDate(@Datedatetime)
RETURNSinteger
--返回年的天数可判断平(365)、润(366)年
begin
returndatediff(day,dateadd(year,datediff(year,0,@Date),0),dateadd(year,datediff(year,0,@Date)+1,0))
end

go

createfunctionUDF_DaysOfYear(@Yearinteger)
RETURNSinteger
--返回年的天数可判断平(365)、润(366)年
begin
returndatediff(day,dateadd(year,@year-year(0),0),dateadd(year,@year-year(0)+1,0))
end

go

createfunctionUDF_HalfDay(@Datedatetime)
returnsdatetime
--返回@Date是上午返回@Date的零点,@Date是下午返回@Date的十二点
as
begin
returncasewhendatepart(hour,@Date)<12
                thendateadd(day,datediff(day,0,@Date),0)--上午归到零点
           else
                dateadd(hour,12,dateadd(day,datediff(day,0,@Date),0))--下午归到十二点
      end
end

go

createfunctionUDF_WeekDiff(@StartDatedatetime,@EndDatedatetime)
returnsinteger
--返回[@StartDate,@EndDate]之间周数周日是当周的最后一天
begin
returndatediff(week,@StartDate,@EndDate)--+1
      +casewhen(@@DateFirst+datepart(weekday,@StartDate))%7=1
                  then1
             else
                  0
        end
      -casewhen(@@DateFirst+datepart(weekday,@EndDate))%7=1
                  then11
<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>