仅将日期时间存储在Access数据库中 [英] Store datetime -time only in Access database

问题描述

我有一个vb.net程序，用于更新访问数据库中的时间值。数据库使用OleDB连接。

I have a vb.net program, updating the time value in an access database. The database is connected using OleDB.

基本上是这样：

Dim commandBuilder As New OleDb.OleDbCommandBuilder(dataEventAdapter)

eventDataset.Tables("EventList").Rows(selectedEvent)("EventTime") = Format(dateTimePick.Value, "hh:mm tt")

dataEventAdapter.Update(eventDataset, "EventList")

时间是从日期时间选择器中获取的，它应该只存储时间值。

The time is taken from a datetime picker, and it should store only the time value.

问题是数据库已经有值了，而数据库中只有值了。时间，例如：9：00 AM，但是当我对此进行更新时，它也会获取日期。老实说，我不知道从哪里获得日期。如果我

The problem is, that the database already has values in it, which only has the time, like: 9:00 AM, but when I'm updating with this, it gets the date as well. And honestly I don't know where it gets the date from. If I

MsgBox(Format(dateTimePick.Value, "hh:mm tt"))

我只有时间，什么都没有。

I get only the time, and nothing else.

如何存储

推荐答案

如果查看MS-Access中可用的数据类型，您会发现其中没有类型仅用于时间值，但日期/时间值有一种类型。这意味着Access将始终存储您提供的值的日期和时间。您在观察MS-Access网格时所看到的显示是由表格结构页面中的格式设置控制的，在这里您可以更改它以仅显示数据的时间部分。

If you look at the datatypes available in MS-Access you will find that there isn't a type just for Time values but there is a type for Date/Time values. This means that Access will store always the date AND the time for the values that you supply. The display that you observe looking at the MS-Access grid is controlled by the Format setting in the structure page of your table and here you could change it to show just the Time part of your data.

表示，存在的问题是您没有提供DateTime值，而是一个字符串。访问足够客气（？）不会触发此异常，但是可以补偿添加本身的日期，因此您应该看到所提供的每个值的当天。

Said that, there is the problem that you don't supply a DateTime value, but a string. Access is gracious(?) enough to not trigger an exception for this, but compensates adding a date by itself thus you should see the current day for every value that you supply.

因此，您不必担心如何显示您的值，而应该更多地关注如何将该值传递给数据库。如果只有时间部分对您的程序有意义，那么让数据库引擎将字符串转换回datetime值是不可行的。 （无需讨论此自动化将涉及的本地化问题）

So you shouldn't be concerned about how your value has been displayed, but more on how you pass that value to the database. If only the time part is meaningful for your program then leaving the database engine convert back your string to a datetime value is not an option. (Without talking about the localization issues that this automation will involve)

我建议为Date部分传递一个常量值（例如DateTime.MinValue或1/1/1） ），然后将您的时间添加到该值中。这样，如果最终需要对该数据使用某些查询，就可以轻松忽略日期部分。

I suggest to pass a constant value for the Date part (like DateTime.MinValue or 1/1/1) and add your time to this value. In this way you could easily ignore the date part if you eventually need to use some queries on this data.

Dim dt As DateTime = new DateTime(1,1,1, dateTimePick.Value.Hour, _
                                         dateTimePick.Value.Minute,
                                         dateTimePick.Value.Second)
eventDataset.Tables("EventList").Rows(selectedEvent)("EventTime") = dt

这篇关于仅将日期时间存储在Access数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！