为什么这个 INSERT with DATEDIFF 语句不能正常工作？
INSERT INTO timecrunch.dbo.intervals (IntervalID, Duration)
SELECT ixInterval, DATEDIFF(hour, dtStart, dtEnd) FROM fogbugz.dbo.TimeInterval
WHERE dtEnd is not NULL
IntervalID 是一个 int，Duration 是一个浮点数
我试图将所有每小时的持续时间放入“持续时间”列中...我假设即使它少于一个小时，它也会使用一个分数，这就是为什么我放置一个浮点数？
帮助。
PS：它运行良好，没有错误，但是完成后间隔表仍然是空的...我知道数据在 TimeInterval 表中...
INSERT INTO timecrunch.dbo.intervals (IntervalID, Duration)
SELECT ixInterval, DATEDIFF(hour, dtStart, dtEnd) FROM fogbugz.dbo.TimeInterval
WHERE dtEnd is not NULL
IntervalID is an int, Duration is a float
I am trying to put all the hourly durations into the Duration column... I assume that even if it is less than an hour it will use a fraction which is why I put a float?
Help.
PS: It's running fine no errors, but after it's done the intervals table is still empty... I know the data is in the TimeInterval table though...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
我不确定为什么您的数据没有显示，但 DATEDIFF 不会返回浮点数。 要获得浮点数，您可能需要使用较小的时间单位并除以每分钟的单位数。 例子：
I'm not sure why your data is not showing up, but DATEDIFF is not going to return a float. To get a float, you'll probably want to use a smaller unit of time and divide by the number of your units per minute. Example:
DATEDIFF 返回跨越的日期部分边界数量的 Int。 我希望您能够了解持续时间的下限，除非从 Int 到 Float 的隐式转换存在问题。
发布收到的错误消息也很有帮助。
DATEDIFF returns an Int of the number of date part boundaries crossed. I would expect you to get the floor of the number of hours duration, unless there is an issue with implicit conversion from Int to Float.
It is also helpful to post the error message received.
要插入一小时的实际小数部分，请使用：
DATEDIFF 始终返回一个 INT 值，而不是小数。
To insert actual fractions of an hour, use:
DATEDIFF always returns you an INT value, never a fraction.
尝试使用 INSERT 位单独运行 SELECT，以检查您的查询是否确实返回了一些数据。
编辑：正如其他人所说，DATEDIFF 返回一个 int，而不是一个 float，但这不应阻止 INSERT 插入一些数据。
Try running the SELECT on its own with the INSERT bit, to check that your query is actually returning some data.
Edit: As others have said, DATEDIFF returns an int, not a float, but that shouldn't prevent the INSERT from inserting some data.
首先，运行 select 会得到什么结果？
First what results do you get just from running the select?
在Sql Server中DateDiff返回一个int
（请参阅 MSDN）
In Sql Server DateDiff returns an int
(See MSDN)
DATEDIFF 将返回 int 而不是 float - 这是您遇到的问题吗？
DATEDIFF will return an int not a float - is that the issue you are experiencing?