对许多人来说,每周的第一天是星期一,但让我们说这是不同的一天,比如星期三。
您能否帮助我创建一个函数( Server 2012),该函数返回第一天的日期:
因此,我们给出一个日期,类型( Current,Next,Last),FirstDay(0,1,2,3,4,5,6),并根据类型返回当前或下一天或最后一天的日期。
创建函数dbo.GetStartofWeek (
@FirstDay int, @type VARCHAR(10), @day DATETime ) RETURNS DATE AS BEGIN谢谢
发布于 2014-12-21 00:56:59
我相信这会给你你想要的日期:
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使用下列输入进行测试:
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一种建议是使用一周偏移,而不是使用“最近”/“当前”/“下一步”,后者更干净、更灵活:
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
gohttps://stackoverflow.com/questions/27585642
复制相似问题