CODEWITHSUNDEEP
sql-server
Bill
Bill

Reputation: 761

Need function the gives DateTime for first day of week given the day number for first day of week

For many, first day of work week is Monday, but lets say its a different day, say Wednesday.

Can you help me create a function (SQL Server 2012) that returns the date for the fist day in:

  1. Current Week
  2. Next Week
  3. Last Week

So a function where we give a date, Type(Current, Next, Last), FirstDay(0,1,2,3,4,5,6) and would return a datetime of first day of Current or next or Last day of week depending on type.

CREATE FUNCTION [dbo].[GetStartofWeek] (
@FirstDay int, @type VARCHAR(10), @day DATETime ) RETURNS DATE AS BEGIN

Thanks

Upvotes: 1

Views: 284

Answers (1)

Jeff Ogata
Jeff Ogata

Reputation: 57783

I believe this will give you the dates you are looking for:

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

Tested with the following inputs:

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

One suggestion would be to use a week offset instead of 'Last'/'Current'/'Next', which is a bit cleaner and more flexible:

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

Upvotes: 2

Related Questions