比较日期 - Access和SQL [英] Comparing dates - Access and SQL
问题描述
我有一个带有DATETIME字段的Access表。该字段未格式化为常规，我将其保留为默认值（空白）
我通过导出我的表使用ODBC在Web上创建了一个SQL表。那个字段也是DATETIME。
查看日期时，我看到：
Access正在存储这样的数据：
11/26/2012 4:27:39 PM
我知道这就是它出现的样子，并且Access实际上将数据存储为浮点数。
查看我的SQL表，我看到数据呈现为：
2012-11-27 23:06:59
我正在尝试进行数据比较。我这样编写SQL并且它不起作用：
I have an Access table with a DATETIME field. The field is not formatted as general, I left it as default (blank)
I have created a SQL table on the web using ODBC by exporting my table. That field is also DATETIME.
When viewing the dates, I see that:
Access is storing data like this:
11/26/2012 4:27:39 PM
I am learning that this is just how it appears, and that Access actually stores the data as a floating point number.
Looking at my SQL table, I see the data presented as:
2012-11-27 23:06:59
I am trying to make data comparisons. I wrote SQL like this and it does not work:
推荐答案
两个系统将日期存储为double，并且它们的格式和呈现方式无关紧要。
现在我没有太多的SQL服务器经验，但我认为可能有一些关于SQL服务器和访问使用不同的零点作为参考。你可能想稍微研究一下。
Both systems store the date as a double, and it doesn''t matter how they are formatted and presented.
Now I don''t have much SQL server experience, but I think there might be something about SQL server and access using different zero points for reference. You might want to look into that a bit.
笑脸的观点似乎很有趣。如果您发现是这种情况，请告诉我们。我必须说如果是这样的话我会感到惊讶（笑脸知道他的东西 - 只是如果那是真的我会期待更多的问题。）
至于哈希，这是很好的思考，但哈希仅用于日期文字（参见 Literal DateTimes及其分隔符（＃））。你没有使用文字，所以不必担心它们。
你有任何表现数据的行为方式你不会期望吗？我的期望是这个SQL应该按原样运行。我对您遇到的问题的唯一猜测是，数据值可能与您预期的完全不同。也许在某些记录上只有Null或者可能是日期或时间的值。
Smiley''s point seems interesting. Let us know if you find that to be the case. I must say I''d be surprised if it were so (Smiley knows his stuff - It''s just that I''d expect more problems if that were true).
As for the hashes, it''s good thinking, but hashes are only used for date literals (See Literal DateTimes and Their Delimiters (#)). You aren''t using literals, so needn''t worry about them.
Do you have any example data that is behaving in a way you wouldn''t expect? My expectation would be that this SQL should work as is. My only guess as to the problems you''re experiencing is that maybe the data values are not exactly as you expect them to be. Maybe Nulls or maybe date- or time-only values on some records.
我有点急，所以我承认不会详细阅读这篇文章，但我认为无论如何你都会发现它有用：
SQL-Server-and-Access-Zero-Date-difference
请告诉我们它是如何工作的。
祝你的项目好运。
I am in a bit of a rush, so I will admit to not reading the article at length, but I think you may find it helpfull regardless:
SQL-Server-and-Access-Zero-Date-Difference
Please let us know how it works out.
Best of luck with your project.
这篇关于比较日期 - Access和SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
