如何从当前月份中选择当前日期 [英] How to select current dates from current month

问题描述

我想检索当前月份1到30之间的数据[我使用MSACCESS Dbase这样做]以下是我正在尝试的查询 -

I would like to retrieve the data between 1 -30 of the current month [ i am using MSACCESS Dbase to do so] Below is the query that i am trying --

SELECT count(usercategory) as category_count ,usercategory  FROM user_category 
where IssueDate between DATEADD('m', DATEDIFF('m', 0, DATE()) - 0 , 0) and  DATEADD('m',     DATEDIFF('m', 0, DATE()) + 1, - 1 ) group by usercategory

我在MSACCESS Dbase中保存的数据

Data that i am holding in my MSACCESS Dbase -

Category1   9/7/2013 12:00:00 AM
Category1   9/8/2013 12:00:00 AM
Category2   10/8/2013 12:00:00 AM

所以输出应该只有2条记录
，但我的查询没有给出任何结果

so output should have only 2 records but my query is giving no results

推荐答案

这里是我认为你需要的查询。它使用的所有函数总是在Access SQL中可用，无论查询是从Access会话还是从无（如在您的c＃情况下）运行。

Here is the query I think you need. All the functions it uses are always available in Access SQL regardless of whether the query is run from within an Access session or from without (as in your c# situation).

数据库引擎会一次计算 DateSerial 表达式，然后使用结果过滤结果集。这种方法将特别快，在 IssueDate 上的索引。

The db engine will evaluate both those DateSerial expressions once, then use their results to filter the result set. This approach will be especially fast with an index on IssueDate.

SELECT
    Count(usercategory) AS category_count,
    usercategory
FROM user_category 
WHERE
        IssueDate >= DateSerial(Year(Date()), Month(Date()), 1)
    AND IssueDate < DateSerial(Year(Date()), Month(Date()) + 1, 0)
GROUP BY usercategory;

这里是一个Access立即窗口会话，解释这些 DateSerial 表达式...

Here is an Access Immediate window session which explains the logic for those DateSerial expressions ...

? Date()
9/6/2013 
? Year(Date())
 2013 
? Month(Date())
 9 
' get the date for first of this month ...
? DateSerial(Year(Date()), Month(Date()), 1)
9/1/2013 
' now get the date for the last of this month ...
? DateSerial(Year(Date()), Month(Date()) + 1, 0)
9/30/2013 

这篇关于如何从当前月份中选择当前日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！