Count data from last week in sql server

nimxete2 于 2023-03-11 发布在 SQL Server

关注(0)|答案(2)|浏览(162)

I'm trying to do a count on all of the data from last week (Monday - Sunday). But it seems like it's ignoring Sunday for some reason and this way I'm not getting the correct count:

SET DATEFIRST 1
    Select *
    FROM Products a
    inner join Locations b
    on a.Location = b.LocID
    where b.LocID = 12 AND
    a.Created >= DATEADD(week, DATEDIFF(week,0,GETDATE())-1,-1)
    AND a.Created < DATEADD(week, DATEDIFF(week,0,GETDATE()),-1)

sql-server

来源：https://stackoverflow.com/questions/44892245/count-data-from-last-week-in-sql-server

2条答案

按热度按时间

zfciruhq1#

Try using day of week instead. I think you might have trouble with using WEEK as you actually need to calculate:
where a.Created >= (last week Monday) and a.Created < (this week Monday)

Here is the query using DW:

SET DATEFIRST 1
Select *
FROM Products a
inner join Locations b
on a.Location = b.LocID
where b.LocID = 12 AND
a.Created >= DATEADD(day, -DATEPART(dw, GETDATE())-6, CONVERT(DATE,GETDATE()))
AND a.Created < DATEADD(day, -DATEPART(dw, GETDATE())+1, CONVERT(DATE,GETDATE()))

赞(0）回复(0）举报 2023-03-11

xzv2uavs2#

Assuming that you have a dateTime column in your table, you can make use of the DATE_SUB() and NOW() functions to get all the data from the last week.

SELECT * FROM your_table
WHERE dateTime_column >= DATE_SUB(NOW(), INTERVAL 1 WEEK);

赞(0）回复(0）举报 2023-03-11

我来回答

Count data from last week in sql server

2条答案

相关问题

热门标签

最新问答