讨论：SQL日期文字和区域设置 [英] Discussion: SQL Date Literals and Regional Settings
问题描述
** NB **此主题与技术问题分开（有关故障处理日期的帮助。。
SQL中的日期不能使用区域设置。
这就是为什么我们在这里多次得到这个问题（或者类似的）。专家给出的许多'解决方案'似乎忽略了这一点并没有帮助。当然，主要原因是SQL标准格式（正如我所说，独立于Region）与美国的区域格式（IE.m / d / yyyy）相同。这意味着世界上很大一部分人从来没有看到将日期写入SQL（过滤器当然也包含SQL指令）所涉及的问题。 Access使用区域设置来显示日期，因此需要明确格式化日期文字以在SQL中可靠地工作（即使在错误的格式下，即使在美国以外的日期也经常被正确解释，因为SQL引擎识别2011年11月22日的日期真的是2011年11月22日，因为一年只有12个月。
有关详细信息，请参阅 Literal DateTimes及其分隔符（＃）。
PS。要正确格式化日期，您始终可以使用以下内容：
** NB ** This thread was split away from a technical question (Help on trouble handling date.).
Dates in SQL don''t use Regional Settings.
This is why we get this question (or similar) so many times here. It doesn''t help that many ''solutions'' given out by experts seem to ignore this. The main reason for this, of course, is that the SQL standard format (which, as I said, is independent of Region) is the same as the Regional format for the USA (IE. m/d/yyyy). This means that a large part of the world never sees the problems involved with writing dates into SQL (and filters also contain SQL instructions of course) just as they come. Access uses Regional Settings to display dates, so date literals need to be formatted explicitly to work reliably in SQL (Even outside of the USA dates are often interpreted correctly even though in the wrong format because the SQL engine recognises dates such as 22/11/2011 as really being 11/22/2011 because there are only twelve months in a year).
For more on this see Literal DateTimes and Their Delimiters (#).
PS. To format a date correctly you can always use the following :
推荐答案
抱歉NeoPa。
我担心它比那复杂一点。你看，如果我使用我的区域设置，并且我在日，月和年之间显示的字符的设置与VBA / SQL引擎不兼容，那么问题就在眼前。
例如，如果我要使用立即窗格：
Sorry NeoPa.
Im afraid its a bit more complicated then that. You see, if I with my regional settings, and my settings for what charecter I want shown between the day, month and year is not compatible with the VBA/SQL engine trouble is just around the corner.
For example if I were to use the immediate pane:
哇笑脸。那令人震惊。这段时间以来我一直都认为我对这一切都很了解，但我的经验几乎全部都是关于使用斜线和美国的欧洲国家。即使订单发生变化，也会在任何地方使用斜杠（/）。所以即使我的'便携式'代码也不是100％便携！这是我不得不承认的一个令人讨厌的打击。
我会优先考虑这个问题，看看我能想出什么（如果我找到你的两个就可以了做我的同伙并为我测试。这将非常有帮助。）
Wow Smiley. That was a shock. All this time I thought I had that all well understood, but my experience has nearly all been about European countries which use slashes and the USA. Everywhere with slashes (/) even if the order changes. So even my ''portable'' code is not 100% portable! That''s a nasty blow I have to admit.
I will look at this as a matter of priority and see what I can come up with (and if I find anything you two can be my accomplices and test it out for me. That would be very helpful).
这太可怕了。我想我可能已经找到了一个可行的解决方案，但我最近纠正了一个人使用它（我刚才解释说这不是真的 - 但我似乎错了）。
无论如何，如果你愿意的话，请为我尝试以下格式（即时窗格应该这样做，但是如果你有一个SQL字符串也可以在其中尝试，那就更好了。）：
This is horrible. I think I may have found a viable solution, but I corrected someone recently for using it (I just explained it wasn''t necessary really - but I was quite wrong it seems).
Anyway, try the following format for me if you would guys (The Immediate Pane should do, but if you have a SQL string to try it out within too that would be even better.) :
这篇关于讨论：SQL日期文字和区域设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
