VB.NET访问日期时间查询问题

问题描述：

我在带有日期时间字段的Access db表中有一堆记录

I have a bunch of records in an Access db table with a datetime fields

例如记录(2/2/2015 3:34:21 PM，2/2/2015 8:29:13 AM)

e.g of records (2/2/2015 3:34:21 PM,2/2/2015 8:29:13 AM )

问题是我需要运行一个查询，在这里我需要将所有记录都显示为在同一天发生的记录，而不考虑时间.如何最好地构造此查询?

Problem is I need to run a query where I need all records for displayed to be ones that occurred on the same day regardless of the time. How to best structure this query?

我使用了从时间= 2/2/2015的表中选择*"，没有返回任何结果.我将日期格式切换为以年份开始，没有运气.

I used 'Select * from table where thetime = 2/2/2015' and there was no result returned. I switched the date format to start with the year, no luck.

有关Access的sql查询语法的任何提示将不胜感激.谢谢.

Any tips as to sql query syntax for Access will be appreciated. Thanks.

答

Access中的日期/时间值始终具有日期和时间部分，因此像 2015-02-02 这样的日期文字是等同于 2015-02-02 00:00:00 .如果想要该日期的所有行，而不考虑时间，则需要使用WHERE子句，例如

Date/Time values in Access always have both a date and time component, so a date literal like 2015-02-02 is equivalent to 2015-02-02 00:00:00. If you want all rows for that date, regardless of the time, you need to use a WHERE clause like

... WHERE thetime >= {that date} AND thetime < {the following day}

在VB.NET中执行此操作的正确方法是使用这样的参数化查询:

The proper way to do that in VB.NET is to use a parameterized query like this:

Using cmd As New OleDbCommand()
    cmd.Connection = con  ' an open OleDbConnection
    cmd.CommandText =
            "SELECT * FROM thetable " &
            "WHERE thetime >= ? AND thetime < ?"
    Dim targetDate As New DateTime(2015, 2, 2)  ' example data
    cmd.Parameters.Add("?", OleDbType.DBTimeStamp).Value = targetDate
    cmd.Parameters.Add("?", OleDbType.DBTimeStamp).Value = targetDate.AddDays(1)
    Using rdr As OleDbDataReader = cmd.ExecuteReader
        Do While rdr.Read()
            Console.WriteLine(rdr("thetime"))
        Loop
    End Using
End Using

VB.NET访问日期时间查询问题

相关推荐