如何在查询中筛选少于7天的每周报告

Question

这是我的查询

SELECT 
    StudentID, DateRegistered 
FROM 
    StudentTable 
WHERE 
    DateDiff(day, DateRegistered, '" & Date.Now & "') <= 7

Answer 1

您没有标记RDBMS，每个RDBMS都有不同的日期函数，但其中一个应该可以正常工作：

SELECT StudentID, DateRegistered 
FROM StudentTable 
WHERE DateDiff(day, DateRegistered, now()) <= 7

或

    WHERE DateDiff(day, DateRegistered, getdate()) <= 7

或者这个：

CURDATE()

请注意，我使用本地DBMS来选择日期，不需要在PHP端使用paramether。

Answer 2

日期应为"YYYY-MM-dd"格式。

SELECT 
    StudentID, DateRegistered 
FROM 
    StudentTable 
WHERE 
    DateDiff(day, DateRegistered, '" & DateTime.Now.ToString('yyyy-MM-dd') & "') <= 7

Answer 3

cmd = "SELECT StudentID, DateRegistered 
FROM StudentTable 
WHERE DateRegistered between @startdate and @enddate", connectionstring)
cmd.parameters.addwithvalue("@StartDate",today.date.subtract(timespan.fromdays(7)))
cmd.parameters.addwithvalue("@EndDate",today)