'Data for last week from Monday to Sunday- Available data is Datetime column

I have column date time available in following format '2022-02-28 08:30:08.000"

I would like run a SQL query to help me get data for last week from Monday to Sunday basis ongoing

Please help

sqlsql-server


Solution 1:[1]

You can try to play around with DATEPART

Below script should return all data for current week.

-- below line will set Monday as first day of the week
set datefirst 1;
SELECT * 
FROM YOUR_TABLE
WHERE DATEPART(ISOWK, YOUR_COLUMN) = DATEPART(ISOWK, GETDATE())
    AND DATEPART(YEAR, YOUR_COLUMN) = DATEPART(YEAR, GETDATE())

Below should return data for whole previous week...

set datefirst 1;
SELECT * 
FROM YOUR_TABLE
WHERE DATEPART(ISOWK, YOUR_COLUMN) = DATEPART(ISOWK, DATEADD(DAY, -7, GETDATE()))
    AND DATEPART(YEAR, YOUR_COLUMN) = DATEPART(YEAR, DATEADD(DAY, -7, GETDATE()))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions