Hi, I would like to do a SELECT of SQL server but only select me the last days from Monday to Sunday. NOT the last 7 days would be something like this:
select GETDATE() - 7
but in that only I get the last 7 days but I want to start from Monday and end the days Sunday
How do I make that select to show me, if I'm on Tuesday, only SUNDAY and MONDAY without counting on Tuesday until it's over?
You can use WEEKDAY to get the day relative to the week (from 1 to 7). This numbering varies by region, so we must standardize it first using SET DATEFIRST . What we do in the following example is to subtract this number from the date of the parameter, such that it fits Monday or Sunday.
SET DATEFIRST 1 -- 1 Lunes, 7 Domingo
DECLARE @Fecha DATE = GETDATE()
SELECT
LunesPasadoMasCercano = DATEADD(
DAY,
1 - DATEPART(WEEKDAY, @Fecha),
@Fecha),
DomingoSiguiente = DATEADD(
DAY,
7 - DATEPART(WEEKDAY, @Fecha),
@Fecha)
Keep in mind that if you are going to do a range filter, you should do it between Monday (inclusive) and Monday after (not included), by a time issue in columns DATETIME , and do it against dates of type DATE or have hour 00:00 .