在VB.NET中访问BETWEEN语句似乎有问题

本文介绍了在VB.NET中访问BETWEEN语句似乎有问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我刚刚发现如果在ADO.NET查询中我使用： "从发票中选择* InvoiceDate BETWEEN StartDate AND EndDate;" 在这种情况下，StartDate将是月份的第1天，EndDate将是该月的最后一天，例如2005年8月31日（英国DD / MM / YYYY格式） 我发现在本月31日的b / b $ b上完成的任何发票都缺少查询，换句话说，如果在声明之间我没有指定 时间，ADO.NET或Jet假设我的意思是31/08/2005 00:00:00。 这是反直觉的，我肯定不同于ADO如何使用 VB6，我发现我现在必须使用31/08/2005 23:59： 59作为结束日期，或者是 我在这里遗漏了什么？ - Siv Martley，英国伍斯特附近。

Hi, I just discovered that if in an ADO.NET query I use: "Select * from Invoices Where InvoiceDate BETWEEN StartDate AND EndDate;" In this case StartDate would be 1st of month and EndDate would be the last day of the month, e.g. 31/08/2005 (UK DD/MM/YYYY format) I find that the query is missing off any invoices that were done on the 31st of the Month, in other words if in the between statement I don''t specify a time, ADO.NET or Jet is assuming I mean 31/08/2005 00:00:00. This is counter intuitive and I''m sure is different to how ADO worked with VB6, I am finding I now must use 31/08/2005 23:59:59 as the end date, or am I missing something here?? -- Siv Martley, Near Worcester, UK.

推荐答案

vb的语法一般是直观的吗？ It可能看起来反直觉对你来说，但如果你真的花时间停下来思考它，那就很有意义了。你在2005年1月8日00:00:00到31/8/2005 00:00:00之间要求发票 如果查询直观地工作了如果我想包括在午夜时发出的所有发票，我该怎么办？但是没有更进一步？ 也许这个缺陷在于你的发票日期。如果您的invoicedate字段 数据没有携带时间元素，那么您的查询将给出结果，您希望。您真的需要知道发票的时间吗？ 或者，不要使用Between运算符。它本质上是邪恶的，并且只包括为了向后兼容算盘。 而是考虑InvoiceDate> = Startdate和InvoiceDate<（EndDate + 1） Gadzukes，有这么多选择！ 留言< eD ************** @ TK2MSFTNGP12.phx.gbl> ，Siv < ms ********** @ removeme.sivill>写道 And vb syntax in general is intuitive? It may seem "counter intuitive" to you, but it makes good sense if you really take the time to stop and think about it. You asked for invoices between 1/8/2005 00:00:00 and 31/8/2005 00:00:00 If the query worked "intuitively" what would I do if I wanted to include all invoices put on at midnight, but no further? Perhaps the flaw lies in your invoice date. If your invoicedate field data didn''t carry the time element your query would give the result you expect. Do you really need to know the time of the invoice? Alternatively, dont use the between operator. It is inherently evil and only included for backward compatibility for the abacus. Instead consider InvoiceDate>=Startdate and InvoiceDate<(EndDate+1) Gadzukes, so many choices! In message <eD**************@TK2MSFTNGP12.phx.gbl>, Siv <ms**********@removeme.sivill> writes 我刚刚发现，如果在ADO.NET查询中，我使用： 从发票中选择*，其中InvoiceDate BETWEEN StartDate和EndDate; 在这种情况下，StartDate将是月份的第1天，EndDate将是该月的最后一天，例如2005年8月31日（英国DD / MM / YYYY格式）我发现在本月31日完成的任何发票中都缺少查询，换句话说，如果在中间声明中我没有指定时间，ADO.NET或Jet假设我的意思是31/08/2005 00:00:00。 这是反直觉的，我是肯定不同于ADO如何使用VB6，我发现我现在必须使用31/08/2005 23:59:59作为结束日期，或者我在这里错过了什么？ Hi,I just discovered that if in an ADO.NET query I use:"Select * from Invoices Where InvoiceDate BETWEEN StartDate AND EndDate;"In this case StartDate would be 1st of month and EndDate would be the lastday of the month, e.g. 31/08/2005 (UK DD/MM/YYYY format)I find that the query is missing off any invoices that were done on the 31stof the Month, in other words if in the between statement I don''t specify atime, ADO.NET or Jet is assuming I mean 31/08/2005 00:00:00.This is counter intuitive and I''m sure is different to how ADO worked withVB6, I am finding I now must use 31/08/2005 23:59:59 as the end date, or amI missing something here??

- Chris Petchey

-- Chris Petchey

Siv写道： Siv wrote: 我刚刚发现，如果在ADO.NET查询中我使用： 从发票中选择* InvoiceDate BETWEEN StartDate和EndDate ; 在这种情况下，StartDate将是月份的第1天，EndDate将是该月的最后一天，例如2005年8月31日（英国DD / MM / YYYY格式）我发现在本月31日完成的任何发票中都缺少查询，换句话说，如果在中间声明中我没有指定时间，ADO.NET或Jet假设我的意思是31/08/2005 00:00:00。 那就是你所说的，所以你几乎不能责怪系统因为你的意思而花费了b / b $ /> '1和3之间是3.5'吗？没有。 这是反直觉的，我肯定与ADO的工作方式不同。 VB6， 你确定是错误的。亲自检查一下吧！ 我发现我现在必须使用31/08/2005 23:59:59作为结束日期，或者我在这里错过了什么？ Hi, I just discovered that if in an ADO.NET query I use: "Select * from Invoices Where InvoiceDate BETWEEN StartDate AND EndDate;" In this case StartDate would be 1st of month and EndDate would be the last day of the month, e.g. 31/08/2005 (UK DD/MM/YYYY format) I find that the query is missing off any invoices that were done on the 31st of the Month, in other words if in the between statement I don''t specify a time, ADO.NET or Jet is assuming I mean 31/08/2005 00:00:00. That''s what you said, so you can hardly blame the system for taking that to be what you meant. Is 3.5 ''between'' 1 and 3 ? No. This is counter intuitive and I''m sure is different to how ADO worked with VB6, Youre sureness is misplaced. Check it yourself and see! I am finding I now must use 31/08/2005 23:59:59 as the end date, or am I missing something here??

我猜这个难题的根源是，将Access中的DateTime 字段视为日期字段（注意后者是 实际上并不存在）。您命名为InvoiceDate的字段实际上包含 日期和时间，应该这样对待。如果你不关心时间，要么确保只有存储纯日期;或者当你不在乎时，明确地删除 时间部分的日期和时间值。 - Larry Lard 回复团体请

This root of your difficulty is, I would guess, treating DateTime fields in Access as if they were Date fields (note that the latter do not actually exist). The field you name ''InvoiceDate'' actually holds a date-and-time, and should be treated as such. Either be sure to only store pure dates if you don''t care about times; or explicitly drop the time part of date-and-time values when you don''t care about it. -- Larry Lard Replies to group please

Chris Petchey写道： [snippage] Chris Petchey wrote: [snippage] 或者，不要使用between运算符。它本质上是邪恶的，只包括为了向后兼容算盘。 Alternatively, dont use the between operator. It is inherently evil and only included for backward compatibility for the abacus.

为什么这么说？ - Larry Lard 回复团体请

Why do you say this? -- Larry Lard Replies to group please