SQL Get the start and the end day of the week with a known datename

Question

Have a look on my function please let me know where is the mistake or is there any easy way to do this

DECLARE @Day varchar(20);
DECLARE @Today varchar(20);
DECLARE @StartDate datetime;
DECLARE @EndDate datetime;
DECLARE @NOW datetime;

SET @NOW=GETUTCDATE();
SET @Today=CONVERT(varchar(20),(select datename(dw,@NOW)))
SET @Day='Monday';

DECLARE @intFlag INT

SET @intFlag = 0

WHILE (@intFlag <7)

    BEGIN
        IF @Today=@Day

            BEGIN
                SET @StartDate=DATEADD(day,-6,@NOW);
                SET @EndDate=DATEADD(day,0,@NOW);
                PRINT @StartDate;
            BREAK;
            END
     SET @NOW=DATEADD(day,1,@NOW);
     SET @intFlag = @intFlag + 1
    END

Is not printing any out put

Thanks

Brett Schneider · Accepted Answer · 2013-11-19T16:16:49.863

there is no output, because you keep changing @NOW, but not @Today. that means you never go into the IF @Today=@Day unless GETUTCDATE() returns a monday. i guessed that since you keep iterating @NOW which yields @Today, you would like to keep @Today on @NOW's weekday.

i took the liberty of adding the update to @Today to your code:

DECLARE @Day varchar(20);
DECLARE @Today varchar(20);
DECLARE @StartDate datetime;
DECLARE @EndDate datetime;
DECLARE @NOW datetime;

SET @NOW=GETUTCDATE();
SET @Today=CONVERT(varchar(20),(select datename(dw,@NOW)))
SET @Day='Monday';

DECLARE @intFlag INT

SET @intFlag = 0

WHILE (@intFlag <7)

    BEGIN
        IF @Today=@Day


            BEGIN
                SET @StartDate=DATEADD(day,-6,@NOW);
                SET @EndDate=DATEADD(day,0,@NOW);
                PRINT @StartDate;
            BREAK;
            END
     SET @NOW=DATEADD(day,1,@NOW);
     SET @Today=CONVERT(varchar(20),(select datename(dw,@NOW)))
     SET @intFlag = @intFlag + 1
    END

EDIT

after reading your commentary, here is some more code. I chose a past tuesday for reference (01.01.1980) so that monday will be a the 0 in the case construct. i know this is not ideal, but try it anyways:

declare @Day varchar(20);
declare @pastweekday datetime;
set @Day = 'Monday';
set @pastweekday = dateadd(day, case when @Day = 'Monday'    then 0 
                                     when @Day = 'Tuesday'   then 1 
                                     when @Day = 'Wednesday' then 2 
                                     when @Day = 'Thursday'  then 3
                                     when @Day = 'Friday'    then 4
                                     when @Day = 'Saturday'  then 5
                                     when @Day = 'Sunday'    then 6
                                end
                              , '19800101')

SELECT DATEADD(day, (DATEDIFF (day, @pastweekday, GETUTCDATE()) / 7) * 7, @pastweekday)

if you just want the first day of the week, use either this: `select CONVERT(varchar(20),dateadd(w, datediff(w, 0, GETUTCDATE()), 0))` which will yield the same format as your routine or `select dateadd(w, datediff(w, 0, GETUTCDATE()), 0);` to just get the datetime of the first day of the week. — Brett Schneider, Nov 19 '13 at 15:18
The think is I know only Day of the week. that is the day of week ending. in my situation like this if day of week ending is FRIDAY then start day is THURSDAY — aas, Nov 19 '13 at 15:23
so you know the day of the week on which the week is ending and wish to use that as the parameter to find the day of the week the current week is beginning? — Brett Schneider, Nov 19 '13 at 16:01
yes thats right but I need a optimized query because is checked when user logging in — aas, Nov 19 '13 at 16:46
thanks anyway do you think while loop reduce the performance? — aas, Nov 19 '13 at 17:47
it kind of does. use the second code that i posted in my answer, it is a rewrite and requires no while-loop. just use `@StartDate =` instead of `select` and then print it. — Brett Schneider, Nov 19 '13 at 20:21

score 1 · Answer 2 · edited May 23 '17 at 11:56

1

What about using this to retrieve the first day of the week ?

SELECT DATEADD(day, DATEPART(weekday, GETUTCDATE()) * -1, GETUTCDATE())

and by the way, a better answer : Get first day of week in SQL Server

edited May 23 '17 at 11:56

Community

1
1

answered Nov 19 '13 at 14:46

rudi bruchez

624
3
10

SQL Get the start and the end day of the week with a known datename

2 Answers2