SQL：列公式
我正在尝试为成本列创建一个公式，该公式根据其他列之间的差异计算成本，但它不起作用。
Parking_Cost 是根据 ParkingStartTime 和 ParkingEndTime * 5 之间的差值计算费用的字段，因为 1 小时 = 5.00 美元。
公式：
(CONVERT([int],[ParkingEndTime]-[ParkingStartTime]*5,(0)))
我真的会感谢任何建议吗？
问候。
I am trying to create a formula for a cost column which calculates the cost based on the difference between other columns and it is not working.
Parking_Cost is the field to have the cost based on the difference between ParkingStartTime and ParkingEndTime * 5 since 1 hours = $5.00.
Formula:
(CONVERT([int],[ParkingEndTime]-[ParkingStartTime]*5,(0)))
I would really appreciate any suggestion?
Regards.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
使用
DATEDIFF确定日期之间的差异/次。您的公式可能是这样的：当然您可以使用
更简单的公式，但在某些情况下它可能会给您带来不想要的结果。例如，如果开始时间为
8:55且结束时间为9:05，则DATEDIFF(hour, ...)将返回1虽然实际差别要小得多，只有 10 分钟。这是因为 DATEDIFF 本质上是将时间截断为指定的单位，然后计算差值。因此，类似的情况可能会发生在几分钟内，但至少在几分钟内你会有更好的粒度。例如，在计算
DATEDIFF(min, '5:20:57', '6:20:04')时，您将收到60的答案，即使它是事实上，略多于 59 分钟，但误差并不像DATEDIFF(hour, ...)那样大。如果您想获得更好的粒度，请计算以秒为单位的差异，然后除以 3600 以获得整个小时：
UPDATE
在 SQL Server 中，当整数除以整数时，结果也将是整数。
如果您需要结果更精确，您可以像这样确保它（使用上面的最后一个表达式）：
Use
DATEDIFFto determine the difference between dates/times. Your formula could be something like this:Of course you could use just
which is simpler, but it might give you undesired results in some cases. For example, if the start time was
8:55and the end time9:05,DATEDIFF(hour, ...)would return1although the actual difference is much less, just 10 minutes. It's because DATEDIFF in essence truncates the times to the units specified and then calculates the difference.So a similar situation can be with minutes, but at least with minutes you would have better granularity. For example, you would receive the answer of
60when calculatingDATEDIFF(minute, '5:20:57', '6:20:04'), even though it's in fact slightly more than 59 minutes, but the inaccuracy is not so big as can be withDATEDIFF(hour, ...).If you want to have even better granularity, calculate the difference in seconds and divide by 3600 to get whole hours:
UPDATE
In SQL Server, when an integer is divided by an integer, the result will be integer too.
If you need the result to be more precise, you can ensure it like this (using the last expression above):