Get end week date function

Question

The start week is Saturday and end week is Friday, I'd like to write a function to return the end week of a date.

e.g.
'2014-6-26' (Thursday) would return '2014-6-27' (Friday)
'2014-6-27' (Friday) would return '2014-6-27' (same day)
'2014-6-28' (Sat) would return '2014-7-04' (next Friday)

Because I cannot SET DATEFIRST in functions, I am struggling to create this function.

This is what I got so far. It works in all scenarios except when I enter a Saturday it returns the previous Friday (which is wrong). e.g. '2014-6-28' returns '2014-6-27'

CREATE FUNCTION [GetEndWeek](
@Date DATETIME
)
RETURNS DATETIME 
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN DATEADD(DAY,(13 - (@@DATEFIRST + DATEPART(WEEKDAY, @Date )))%7,@Date )
END

Any help appreciated.

I do not want to SET DATEFIRST anywhere else (i.e. in procs) as I'm calling this function a lot.

Thanks!

http://stackoverflow.com/a/1267458 – Anup Agrawal Jul 21 '14 at 03:41 — Anup Agrawal, Jul 21 '14 at 03:41

Gordon Linoff · Answer 1 · 2014-07-21T11:21:50.403

2

Assuming that the language is English, you should use datename() rather than datepart(). datepart() is subject to system values. I think this logic does what you want:

return dateadd(day,
               (case datename(weekday, @Date)
                     when 'Saturday' then 6
                     when 'Sunday' then 5
                     when 'Monday' then 4
                     when 'Tuesday' then 3
                     when 'Wednesday' then 2
                     when 'Thursday' then 1
                     when 'Friday' then 0
                end),
               @Date);

edited Jul 21 '14 at 11:21

answered Jul 21 '14 at 03:40

Gordon Linoff

1,242,037
58
646
786

Ohh you mean datename instead of datepart! works if i swap around! – viv_acious Jul 21 '14 at 04:09
@Gordan great logic... 1 Up Vote. – Nithin Gangadharan Jul 21 '14 at 05:09
@viv_acious . . . Sorry about that. Got it right in the description but the not query (now fixed). – Gordon Linoff Jul 21 '14 at 11:22

score 0 · Answer 2 · answered Jul 21 '14 at 05:05

I'm not sure the existing function is correct, it gave me the column "prev_func" below, so for Sat Jun 28 I would expect the following Friday (July 4), like this:

|                   ADATE | MODULUS_7 |            NEW_METHOD |             PREV_FUNC |
|-------------------------|-----------|-----------------------|-----------------------|
| Thursday, June 26, 2014 |         1 | Friday, June 27, 2014 | Friday, June 27, 2014 |
|   Friday, June 27, 2014 |         0 | Friday, June 27, 2014 | Friday, June 27, 2014 |
| Saturday, June 28, 2014 |        -1 | Friday, July 04, 2014 | Friday, June 27, 2014 |

You don't have to use @@datefirst or change datefirst to arrive at predictable day of week calculations. The date zero in sql server, 1900-01-01 is a Monday, so datediff(day,0,[date-here]) % 7 is predictable with zero (no remainder) being Monday, and Friday is 4.

I believe the calculation you need is in the query below, column "new_method" :

select
      format(adate,'D') as adate

    , 4 - (datediff(day,0,adate) % 7) as modulus_7

    , format(
             case when 4 - datediff(day,0,adate) % 7 < 0
                then dateadd(day,4 - (datediff(day,0,adate) % 7) + 7,adate)
             else dateadd(day,4 - datediff(day,0,adate) % 7,adate)
             end
      ,'D') as new_method

    , format(dbo.getendweek(adate),'D') as prev_func

from table1
;

see: http://sqlfiddle.com/#!6/0903f/16

Get end week date function

2 Answers2