SQL VBA中日期之间的SQL查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25058768/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Query between dates in VBA
提问by Monchou2
I've a problem making a sql query in VBA using excel and an access database. In mi VBA code I've two data variables with this content:
我在使用 excel 和 access 数据库在 VBA 中进行 sql 查询时遇到问题。在 mi VBA 代码中，我有两个包含此内容的数据变量：
DateMin = 31/07/2014 22:00:00
日期最小值 = 31/07/2014 22:00:00
DateMax = 01/08/2014 06:00:00
日期最大值 = 01/08/2014 06:00:00
And I have an access database with many data with a date field. I'm trying to extract from the database, data with dates between my two variables and I'm coding this:
我有一个包含许多数据和日期字段的访问数据库。我正在尝试从数据库中提取两个变量之间带有日期的数据，我正在对此进行编码：
sql = "SELECT Date FROM Table WHERE Date BETWEEN #" & DateMin & "# AND #" & DateMax & "#
But doesn't works. Results are incorrect, with dates that aren't between my two dates.
但不起作用。结果不正确，日期不在我的两个日期之间。
However if I change the sentence and code this
但是，如果我更改句子并对此进行编码
sql = "SELECT Date FROM Table WHERE Date > #" & DateMin & "#
It works! Gives data with higher dates than DateMin but I haven't DateMax to stop it. I think the problem is in the second part of my first code, because if I code
有用！提供日期比 DateMin 更高的数据，但我没有 DateMax 来阻止它。我认为问题出在我的第一个代码的第二部分，因为如果我编码
sql = "SELECT Date FROM Table WHERE Date < #" & DateMax & "#
I have no data! Why doesn't give me data lower than DateMax? Because of that I think doesn't works the BETWEEN sentence.
我没有数据！为什么不给我低于 DateMax 的数据？因此，我认为 BETWEEN 句子不起作用。
Sorry for my english, i'm trying to explain better I know. Thanks.
对不起我的英语，我正在努力解释我所知道的更好。谢谢。
采纳答案by Kai
You should always use an unambiguousdate format. When you have a date formated 00/00/0000, Access has to guess whether that's dd/mm/yyyy(British) or mm/dd/yyyy(American).
您应该始终使用明确的日期格式。当您有一个格式化的日期时00/00/0000，Access 必须猜测它是dd/mm/yyyy（英国）还是mm/dd/yyyy（美国）。
Your DateMin(31/07/2014 22:00:00) can only be interpreted as dd/mm/yyyy hh:nn:ss, (31 is an invalid month, so it must be a day) so this is the format that Access will use.
您的DateMin( 31/07/2014 22:00:00) 只能解释为dd/mm/yyyy hh:nn:ss,（31 是无效月份，因此必须是一天），因此这是 Access 将使用的格式。
However, your DateMax(01/08/2014 06:00:00) is being interpreted in Americanformat - as 8th January 2014rather than 1st August 2014.
但是，您的DateMax( 01/08/2014 06:00:00) 被解释为美式格式 - as8th January 2014而不是1st August 2014.
The easiest way to get around this is to supply your dates in ISO format (yyyymmdd) or supply the month as a short word (dd mmm yyyy hh:mm:ss- eg 01 Aug 2014 06:00:00)
解决此问题的最简单方法是以 ISO 格式 ( yyyymmdd) 提供日期或以短字提供月份 ( dd mmm yyyy hh:mm:ss- 例如01 Aug 2014 06:00:00)
