英文:
Calculation DateDiff with date and integer
问题
我想计算并统计两个日期之间的小时差，如果它们大于72小时的话。
在数据库中，日期和小时分开存储在两个列中。所以数据库给了我以下的数据：
| 列名 | 类型 |
|---|---|
| 开始日期 | 日期 (dd.mm.yyyy) |
| 开始小时 | 整数 |
| 结束日期 | 日期 (dd.mm.yyyy) |
| 结束小时 | 整数 |
它应该按照以下方式工作：
- 计算两个日期之间的天数差
- 将结果乘以24以得到小时数
- 加上小时差
- 检查结果是否大于72
- 如果是，就计数。如果不是，就不计数。
在报告生成器中，我尝试了以下表达式：
=SUM(
IIF(
(DateDiff("dd", Fields!StartDate.Value, Fields!EndDate.Value) * 24 + Fields!StartHour.Value - Fields!EndHour.Value
)
> 72, 1, 0
)
)
这个表达式不起作用，它返回 #error。所以我认为我需要使用 CInt 将 DateDiff 转换为整数，但根据 Microsoft 文档 中的说明，DateDiff 已经返回一个整数。
另一个问题是简单地减去小时值可能不会产生所期望的结果。然而，我不知道如何解决这个问题。
提前感谢您的帮助。
英文:
I want calculate and then count the difference in hours between two dates with hours if they are bigger than 72.
In the database the dates and hours are seperated in two columns. So the database gives me the following:
| Column | Type |
|---|---|
| StartDate | date (dd.mm.yyyy) |
| StartHour | integer |
| EndDate | date (dd.mm.yyyy) |
| EndHour | integer |
It should work like this:
- Calculate the difference between two dates in days
- Multiply the result by 24 to get hours
- Add the difference in hours
- check if the result is bigger than 72
- if yes, count it. if no, do not count it.
In Report Builder I tired the following expression:
=SUM(
IIF(
(DateDiff("dd",Fields!StartDate.Value,Fields!EndDate.Value) * 24 + Fields!StartHour.Value - Fields!EndHour.Value
)
> 72, 1, 0
)
)
This expressions does not work, it returns #error. So I thought, that I need to convert the DateDiff to Integer with CInt, but according to Microsoft documentation DateDiff already returns an integer.
Another problem is that simply subtracting the hourly values may not produce the desired result. However, I do not know how to solve this.
Thanks in advance.
答案1
得分: 1
Verify that your datediff for the dates works with the following expression:
=DATEDIFF("d",Fields!StartDate.Value,Fields!EndDate.Value)
If not, try converting the date columns to proper date format.
Work every expression in separate column to verify everything works before combining them.
英文:
Verify that your datediff for the dates works with the following expression
=DATEDIFF("d",Fields!StartDate.Value,Fields!EndDate.Value)
If not, try converting the date columns to proper date format.
Work every expression in separate column to verify everything works before combining them.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论