Get timestamp for start of the week and end of the week

Question

In order to get timestamp for start of the week and end of the week I do the following.


    SET @currentTimestamp = DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE());
    SET @currentDate = DATEADD(s, @currentTimestamp, '19700101');
    SET @currentWeek = DATEPART(wk, @currentDate);
    SET @starter = 1451654325; 
    SET @weekSize = 604800; 
    SET @beginOfWeek = @starter + ((@weekSize) * (@currentWeek - 2));
    SET @endOfWeek = @starter + ((@weekSize) * (@currentWeek - 1));

It is necessary for the future for query like...


    Submit_Date BETWEEN @beginOfWeek AND @endOfWeek

For doing something as part of current week.

Can I make the code more elegant and beautiful?

http://stackoverflow.com/questions/12422248/previous-monday-previous-sundays-date-based-on-todays-date — David Garwin, Oct 31 '16 at 20:15
This is probably better suited to http://codereview.stackexchange.com — Brandon, Oct 31 '16 at 20:33

score 0 · Accepted Answer · answered Nov 01 '16 at 10:40

You can use this sub-query results directly in BETWEEEN condition:

SELECT LastSunday, DATEADD(dd, -7, LastSunday) AS SundayBeforeThat
FROM (SELECT CAST(GETUTCDATE() - DATEPART(dw, GETUTCDATE()) + 1 AS DATE) AS LastSunday) A

Here we use CAST(... AS DATE) to truncate time portion;
Deduct DATEPART(dw, GETUTCDATE()) + 1 to get last Sunday;
DATEADD(dd, -7, LastSunday) with negative number to go back one week.

Actually, I would probably INNER JOIN this subquery ON Submit_Date < LastSunday AND Submit_Date >= SundayBeforeThat to filter the target query:

...
INNER JOIN (
        SELECT CAST(GETUTCDATE() - DATEPART(dw, GETUTCDATE()) + 1 AS DATE) AS LastSunday
    ) w
 ON Submit_Date >= DATEADD(dd, -7, w.LastSunday)
AND Submit_Date < w.LastSunday

Get timestamp for start of the week and end of the week

1 Answers1