sql查询选择周,月,天 [英] sql query to select week,month, days
问题描述
请伙计们,我需要一个 sql 查询来使用 vb6 从访问数据库中选择数据.查询只提供当前日期,比较的文件是数据库中的curr_date.
例如
SELECT *从表WHERE curr_date BETWEEN firstoflastmonth 和 endoflastmonth;选择 *从表WHERE curr_date BETWEEN firstoflastweek 和 endoflastweek;选择 *从表WHERE curr_date BETWEEN firstofthismonth 和 endofthismonth;选择 *从表WHERE curr_date BETWEEN firstofthsiweek 和直到日期;
我遇到的问题是如何指定两个日期进行比较
然后检查您希望它在哪个月/周不是最简单的吗?
来自你的例子:
SELECT * FROM TABLEWHERE curr_date BETWEEN firstoflastmonth 和 endoflastmonth;
这将变成(使用 month 函数)>
SELECT * FROM TABLEWHERE 月(curr_date) = 月(dateadd("m",-1,Date()));
这将您的 curr_date 月份与 Date 月份(当前日期)减去一个月,即最后一个月.这也可以使用 datediff 来完成:
SELECT * FROM TABLEWHERE DateDiff ("m", curr_date, date()) = 1AND curr_date <日期();
添加最后一行是为了确保不会选择一个月后的日期.
同样的事情可以使用在这里找到的日期函数应用于其他查询>
链接到 Richard Calahan 提供的参考表
please guys, i need an sql query to select data from an access database using vb6. the query will only provide current date and the filed been compaired is curr_date in the database.
for example
SELECT *
FROM TABLE
WHERE curr_date BETWEEN firstoflastmonth AND endoflasthmonth;
SELECT *
FROM TABLE
WHERE curr_date BETWEEN firstoflastweek AND endoflasthweek;
SELECT *
FROM TABLE
WHERE curr_date BETWEEN firstofthismonth AND endofthismonth;
SELECT *
FROM TABLE
WHERE curr_date BETWEEN firstofthsiweek AND tilldate;
The problem i have figuring ot is how to specify the two dates in comparison
Is it not easiest to just check wich month/week you want it to be in then?
From you examples:
SELECT * FROM TABLE
WHERE curr_date BETWEEN firstoflastmonth AND endoflasthmonth;
This would become (with the month function used)
SELECT * FROM TABLE
WHERE month(curr_date) = month(dateadd("m",-1,Date()));
This compares the month of your curr_date to the month of Date (the current date) minus one month, so the last month. This can also be done using datediff:
SELECT * FROM TABLE
WHERE DateDiff ( "m", curr_date, date()) = 1
AND curr_date < date();
The last line is added to make sure that dates one month later are not selected.
Same thing can be applied to the other queries using the date function found here
Links used to the reference sheet provided by Richard Calahan
这篇关于sql查询选择周,月,天的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!