首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >需要函数给出一周的第一天的DateTime,给定一周的第一天的天数

问需要函数给出一周的第一天的DateTime,给定一周的第一天的天数
EN

Stack Overflow用户
提问于 2014-12-21 00:20:24
回答 1查看 271关注 0票数 1

对许多人来说,每周的第一天是星期一,但让我们说这是不同的一天,比如星期三。

您能否帮助我创建一个函数( Server 2012),该函数返回第一天的日期:

  1. 本星期
  2. 下周
  3. 上周

因此,我们给出一个日期,类型( Current,Next,Last),FirstDay(0,1,2,3,4,5,6),并根据类型返回当前或下一天或最后一天的日期。

创建函数dbo.GetStartofWeek (

代码语言:javascript
复制
 @FirstDay int,     @type VARCHAR(10),     @day DATETime ) RETURNS DATE AS BEGIN

谢谢

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2014-12-21 00:56:59

我相信这会给你你想要的日期:

代码语言:javascript
复制
create function [dbo].[GetStartofWeek] 
(
    @firstDay int, 
    @type varchar(10), 
    @day date
) 
returns date as
begin

-- use datediff/dateadd to get the date of sunday for the week of the given @day (-1 casts as Sunday 1899-12-31)
declare @sundayOfWeek date = dateadd(week, datediff(week, -1, @day), -1)

-- @firstDay is 0 to 6, 0 representing Sunday
declare @firstDayOfWeek date = dateadd(day, @firstDay, @sundayOfWeek)

-- add or subtract a week if necessary
set @type = upper(@type)

if @type = 'LAST'
    set @firstDayOfWeek = dateadd(week, -1, @firstDayOfWeek)
else if @type = 'NEXT'
    set @firstDayOfWeek = dateadd(week, 1, @firstDayOfWeek)

return @firstDayOfWeek
end
go

使用下列输入进行测试:

代码语言:javascript
复制
select [dbo].[GetStartofWeek](0, 'Last',    '2014-12-14')   -- 2014-12-07   
select [dbo].[GetStartofWeek](3, 'Last',    '2014-12-14')   -- 2014-12-10   
select [dbo].[GetStartofWeek](6, 'Last',    '2014-12-14')   -- 2014-12-13   
select [dbo].[GetStartofWeek](0, 'Current', '2014-12-17')   -- 2014-12-14   
select [dbo].[GetStartofWeek](3, 'Current', '2014-12-17')   -- 2014-12-17
select [dbo].[GetStartofWeek](6, 'Current', '2014-12-17')   -- 2014-12-20
select [dbo].[GetStartofWeek](0, 'Next',    '2014-12-20')   -- 2014-12-21
select [dbo].[GetStartofWeek](3, 'Next',    '2014-12-20')   -- 2014-12-24
select [dbo].[GetStartofWeek](6, 'Next',    '2014-12-20')   -- 2014-12-27

一种建议是使用一周偏移,而不是使用“最近”/“当前”/“下一步”,后者更干净、更灵活:

代码语言:javascript
复制
alter function [dbo].[GetStartofWeek] 
(
    @firstDay int, 
    @weekOffset int, 
    @day date
) 
returns date as
begin

-- use datediff/dateadd to get the date of sunday for the week of the given @day (-1 casts as Sunday 1899-12-31)
declare @sundayOfWeek date = dateadd(week, datediff(week, -1, @day), -1)

-- @firstDay is 0 to 6, 0 representing Sunday
declare @firstDayOfWeek date = dateadd(day, @firstDay, @sundayOfWeek)

-- add or subtract weeks if necessary
if @weekOffset <> 0
begin
    set @firstDayOfWeek = dateadd(week, @weekOffset, @firstDayOfWeek)
end

return @firstDayOfWeek
end
go
票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/27585642

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档