Show only full weeks - SQL

Question

how could I display only full weeks from my dataset? So if my week starts on Monday 1st of August to the 14th, that would be considered a full week. Whereas the one starting on the 8th is considered half and I'd like not to show it.

Many thanks in advance!

Table name:RRA date column: seriesdate

Why is the week of 2022-08-08 considered a half-week? It's a full week to me... — Dai, Aug 10 '22 at 11:26
...or do you mean to exclude dates that start in the _current_ ("now") week? — Dai, Aug 10 '22 at 11:27

score 0 · Answer 1 · answered Aug 10 '22 at 11:36

My answer assumes that you want to select rows where the seriesdate value is in a week that is not the current week.
Assuming that your seriesdate column is indexed then a SARGable (i.e. fast) way to implement this filter is to just exclude rows with dates after the start-of-the-current-week. Like so...
Also...
- Note that CURRENT_TIMESTAMP (the ISO SQL equivalent of SQL Server's GETDATE()) uses the database server's local timezone which might not be the timezone used by your data.
  - If so, replace DECLARE @now datetime2(7) = CURRENT_TIMESTAMP; with a date/time-literal like DECLARE @now datetime2(7) = '2022-08-10 12:34:56';
  - Note that most SQL Server setups will default to DATEFIRST 7 (Sunday).

SET DATEFIRST 1; -- https://learn.microsoft.com/en-us/sql/t-sql/statements/set-datefirst-transact-sql

DECLARE @now datetime2(7) = CURRENT_TIMESTAMP; -- or '2022-08-10 12:34:56'

-- https://stackoverflow.com/questions/7168874/get-first-day-of-week-in-sql-server
DECLARE @startOfThisWeek date = DATEADD( day, 1 - DATEPART( weekeday, @now ), @now );

SELECT
    q.*
FROM
    [RRA] AS q
WHERE
    q.[seriesdate] < @startOfThisWeek

Show only full weeks - SQL

1 Answers1