Query - show records where a date field is this week

This month:

WHERE [completed date] >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND
    [completed date] < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)

This week is very similar but depends on how you define a week - what day does your week start on?

I think week is going to be Sunday --> Saturday ...ie just a normal week

could you give me that query too so i don't mess it up?

WHERE [completed date] >= DATEADD(DAY, -DATEDIFF(DAY, 6, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) % 7,DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
    AND [completed date] < DATEADD(DAY, -DATEDIFF(DAY, 6, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) % 7 + 7,DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))

You can simplify it if you add a variable for the current day with time stripped.


DECLARE @today_midnight datetime
SET @today_midnight = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

WHERE [completed date] >= DATEADD(DAY, -DATEDIFF(DAY, 6, @today_midnight) % 7, @today_midnight) AND
    [completed date] < DATEADD(DAY, -DATEDIFF(DAY, 6, @today_midnight) % 7 + 7, @today_midnight)

Similarly, for the month:

DECLARE @start_of_month_midnight
SET @start_of_month_midnight = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

...
WHERE [completed date] >= @start_of_month_midnight AND
     [completed date] < DATEADD(MONTH, 1, @start_of_month_midnight)

>> If you don't have any index on the [completed date] column <<

But an index could be added next week.  It's very poor practice to deliberately exclude indexes from consideration, particularly due to lazy coding.

You really should always avoid using functions on table columns in a WHERE or JOIN whenever possible.  One, ISNULL, should never be used in a WHERE or JOIN, because it can always be easily coded around.

But, if you do use the month,  you need to include a year check for accuracy, or you'd get every year's rows from the table.

But, if you do use the month,  you need to include a year check for accuracy, or you'd get every year's rows from the table.

Yes, that's true. I've missed the year in my suggested solution.

ty