如何仅查询包含两个日期之间的日期数据的行。 [英] How to query only rows containing date data between two dates.

问题描述

好的，所以我没有太多运气就到了谷歌。我正在尝试查询包含此格式日期的列（2014年1月1日，1/2014/2014等）。该列是nchar（我知道应该更改），但我不认为这将是一个问题。发生的事情是当我搜索从2014年1月1日开始的日期时，查询还会返回2014年1月10日，2014年1月11日的结果，依此类推。任何帮助将非常感激！这是我正在使用的简单查询：

展开 | 选择 | Wrap | 行号

解决方案

除非你有一个很好的理由不这样做，我建议你按照更直接的方法存储你想要的数据。 IE浏览器。日期而不是看起来像日期的字符串。


如果必须使用字符串值，那么至少应该以不与值相兼容的方式对其进行格式化（ EG.mm / dd / yyyy而不是m / d / yyyy）。


如果你必须有一个字符串，宁愿过上更轻松的生活，也不要使用标准日期格式化，但是根据需要从左到右对值进行排序。例如。 yyyy / mm / dd。


我会想象使用T-SQL从字符串中提取你想要的值会非常痛苦和混乱。

< blockquote>谢谢，NeoPa。我希望这不是解决这个问题的唯一方法，但它最有意义。我有/无意改变从字符串到日期的格式。这更像是为什么它不起作用？ ...更少如何让它工作？...因为我知道该表已经设置错误了:)非常感谢您的回复！

Are你还有兴趣更多地解释替代建议如何/为什么会起作用？


提出技术问题是完全合理的，你不一定考虑投入实践。如果你想进一步了解那么我很高兴有义务。


我总是会解释一下这不是一个好方法。即使你已经意识到这一点，许多以后发现和阅读的人也许不会。

Okay, so I''ve been all over Google without much luck. I''m trying to query a column that contains dates in this format (1/1/2014, 1/2/2014, etc.). The column is nchar (which I''m aware should be changed), but I didn''t figure this would be an issue. What''s happening is when I search for a date starting at 1/1/2014, the query also returns results with 1/10/2014, 1/11/2014, and so on. Any help would be much appreciated! Here''s the simple query I was using:

Expand|Select|Wrap|Line Numbers

解决方案

Unless you have a very good reason for not doing so, I would suggest you follow the more straightforward approach of storing the data you want. IE. Dates rather than strings that look like dates.

If you must use a string value then you should, at least, format it in such a way that it is compatible regardless of the values (EG. mm/dd/yyyy rather than m/d/yyyy).

If you must have a string and would rather have an easier life, don''t use a standard date format at all, but something that orders the values left-to-right as necessary for sorting. EG. yyyy/mm/dd.

I would imagine that extracting the values you want from your strings using T-SQL would be quite painful and messy.

Thanks, NeoPa. I was hoping that wasn''t the only way to go about this, but it makes the most sense. I had/have every intention of changing from string to date format. This was more of a "why doesn''t it work?" and less of a "how do I make it work?"...since I understand that the table is already set up incorrectly :) Thanks a lot for the response!

Are you still interested in more explanations of how/why the alternative suggestions would work?

It''s perfectly reasonable to ask technical questions that you aren''t, necessarily, considering putting into practice. If you want further understanding then I''m happy to oblige.

I would always explain the caveat that it isn''t a good approach though. Even if you appreciate that already, many that find and read this later on may not.

这篇关于如何仅查询包含两个日期之间的日期数据的行。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！