在 SQL 中与 Dateiff 作斗争
我正在用 SQL 编写一个小查询，并且遇到了一个似乎有人以前遇到过的问题。我想找出两个日期之间的月数。我正在使用像 ... 这样的表达式，
DATEDIFF(m,{firstdate},{seconddate})
但是我注意到这个函数正在计算日期超过每月阈值的次数。例如...
DATEDIFF(m,3/31/2011,4/1/2011) will yield 1
DATEDIFF(m,4/1/2011,4/30/2011) will yield 0
DATEDIFF(m,3/1/2011,4/30/2011) will yield 1
有谁知道如何根据经过的时间然后超过每月阈值的时间来查找两个日期之间的月份？
I am writing a little query in SQL and am butting heads with an issue that it seems like someone must have run into before. I am trying to find the number of months between two dates. I am using an expression like ...
DATEDIFF(m,{firstdate},{seconddate})
However I notice that this function is tallying the times the date crosses the monthly threshold. In example...
DATEDIFF(m,3/31/2011,4/1/2011) will yield 1
DATEDIFF(m,4/1/2011,4/30/2011) will yield 0
DATEDIFF(m,3/1/2011,4/30/2011) will yield 1
Does anyone know how to find the months between two dates more-so based upon time passed then times passed the monthly threshold?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
如果您想查找名义上的月份数，为什么不查找天数差异，然后除以 30（根据需要转换为 FLOAT）。或者 30.5 左右 - 取决于您想要如何处理全年可变的月份长度。但也许这不是您的具体情况的一个因素。
If you want to find some notional number of months, why not find the difference in days, then divide by 30 (cast to FLOAT as required). Or 30.5-ish perhaps - depends on how you want to handle the variable month length throughout the year. But perhaps that's not a factor in your particular case.
（来自 DATEDIFF，datepart 边界< /强>）。如果您对此不满意，您可能需要按照 martinclayton 的建议使用天作为单位
(from DATEDIFF, section datepart Boundaries ). If you are not satisfied by it, you probably need to use days as unit as proposed by martin clayton
DATEDIFF 的设计就是这样的。在评估特定时间测量值（例如月或天等）时，它仅考虑该测量值和较高的值 - 忽略较小的值。任何时间测量都会遇到这种行为。例如，如果您使用 DATEDIFF 来计算天数，并且一个日期在午夜前几秒，另一个日期在午夜后几秒，您将得到“1”天的差异，即使这两个日期只有几秒。相隔几秒。
DATEDIFF 旨在给出问题的粗略答案，如下所示：
问题：您多大了？
答案：某个整数。你不会说“我今年59岁4个月17天5小时35分27秒”。你只要说“我59岁了”。这也是 DATEDIFF 的方法。
如果您想要一个根据上下文含义量身定制的答案（例如您的儿子说“我不是 8 岁！我已经 8 又四分之三了！或者我几乎 9 岁了！），那么你应该查看下一个最小的测量值并用它进行近似，所以如果你是在几个月之后，那么在天或小时上做一个 DATEDIFF ，并尝试近似几个月，但它似乎与你的情况最相关（也许你）。想要答案例如 1-1/2 个月，或 1.2 个月等）使用 CASE / IF-THEN 类型的逻辑。
DATEDIFF is like this by design. When evaluating a particular time measurement (like months, or days, etc.), it considers only that measurement and higher values -- ignoring smaller ones. You'll run into this behavior with any time measurement. For example, if you used DATEDIFF to calculate days, and had one date a few seconds before midnight, and another date a few seconds after midnight, you'd get a "1" day difference, even though the two dates were only a few seconds apart.
DATEDIFF is meant to give a rough answer to questions, like this:
Question: how many years old are you?
Answer: some integer. You don't say "I'm 59 years, 4 months, 17 days, 5 hours, 35 minutes and 27 seconds old". You just say "I'm 59 years old". That's DATEDIFF's approach too.
If you want an answer that's tailored to some contextual meaning (like your son who says "I'm not 8! I'm 8 and 3-quarters!, or I'm almost 9!), then you should look at the next-smallest measurement and approximate with it. So if it's months you're after, then do a DATEDIFF on days or hours instead, and try to approximate months however it seems most relevant to your situation (maybe you want answers like 1-1/2 months, or 1.2 months, etc.) using CASE / IF-THEN kinds of logic.