MS Access - 选择Char作为日期并执行日期差异

I have two columns. ColA and ColB contains char(10) with data "20090520" and "20090521".

我有两列。 ColA和ColB包含char(10),数据为“20090520”和“20090521”。

I want to select and get the date difference in days. I have tried using Format() and CDate() but MS Access always display as #ERROR.

我想选择并获得日期差异。我尝试使用Format()和CDate(),但MS Access始终显示为#ERROR。

4 个解决方案

#1

Access prefers its dates in this format:

Access以这种格式更喜欢其日期:

#2009-12-01#

You can convert your date to something Access understands with:

您可以将您的日期转换为Access了解的内容:

CDate(Format([ColA], "0000-00-00"))

Or alternatively:

DateSerial(Left([ColA],4),Mid([ColA],5,2),Right([ColA],2))

And to display the result in your preferred format:

并以您首选的格式显示结果:

Format(<date here>, "dd-mm-yyyy")

#2

Try using DateSerial() to convert the dates:

尝试使用DateSerial()转换日期:

DateSerial(Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2))

#3

If at all possible, change the datatype to a date datatype. You should not store dates as character data.

如果可能,请将数据类型更改为date数据类型。您不应将日期存储为字符数据。

#4

I am connecting to another database which I have no control on. That is why this problem occurred. Thanks for the feedback.

我正在连接另一个我无法控制的数据库。这就是出现这个问题的原因。感谢您的反馈。

#1