问题描述
我有一个 SSRS 日期/时间参数,它是从针对 SQL Server 日期时间字段的共享数据集查询中生成的.该参数在报表文本框中正确显示,但在嵌入式数据集查询中不起作用,即使是针对生成日期时间值的同一个表也是如此.
I have an SSRS Date/Time parameter generated from a shared dataset query against a SQL Server datetime field. The parameter displays correctly in a report textbox but it will not work in an embedded dataset query, even against the same table that the datetime value was generated from.
为了在数据集查询中使用该参数,我必须解析 where 子句的两侧才能使其在 SSDT 的预览中工作:
In order to use the parameter for a dataset query I have to parse both sides of a where clause to get it to work in Preview in SSDT:
(convert(varchar,invoice.snapshot_datetime,120)) = (convert(varchar,@snapshotdatetime,120))
这是非常低效的.
如何在不解析 invoice.snapshot_datetime
列的情况下使我的 where 子句起作用?
How can I get my where clause to work without parsing the invoice.snapshot_datetime
column?
服务器详情
- SQL Server 语言是英语(美国).
- SQL Server
dateformat
是mdy
(来自dbcc useroptions
). Getdate()
在 SSMS 中返回'2015-05-20 10:27:56.687'
- The SQL Server Language is English (United States).
- SQL Server
dateformat
ismdy
(fromdbcc useroptions
). Getdate()
returns'2015-05-20 10:27:56.687'
in SSMS
推荐答案
假设您的日期范围介于 1900-01-01 和 2079-06-06 之间,您可以投射到 SmallDateTime
截断 datetime
变量中的秒数:
Assuming your date range is between 1900-01-01 and 2079-06-06 you can cast to SmallDateTime
to truncate the seconds out of your datetime
variable:
DECLARE @DateTime datetime
SET @DateTime = CAST(CAST(@snapshotdatetime as SmallDateTime) as DateTime)
(感谢 t-clausen.dk 对于 他的回答在这里)
现在,由于您的实际列的类型为 DateTime代码>
,它确实保留了秒(和毫秒),您也需要消除它们.
但是,在您的列上使用函数将阻止 SQL Server 使用您在该列上可能拥有的任何索引,因此更好的方法是使用 DateTime
范围:
Now, since your actual column is of type DateTime
, it does keep seconds (and milliseconds), and you will need to eliminate them as well.
However, using functions on your column will prevent the SQL Server from using any indexes you might have on this column, so a better approach would be to use a DateTime
range:
DECLARE @FromDateTime datetime, @ToDateTime datetime
SET @FromDateTime = CAST(CAST(@snapshotdatetime as SmallDateTime) as DateTime)
因为如果超过 29.998 秒,演员会将小日期时间的分钟向上舍入,如果低于 29.999 秒,则向下舍入.你总是想四舍五入,因为它是 From 日期时间,你需要检查是否需要减少一分钟:
Since the cast will round the minutes of the small date time up if it's over 29.998 seconds, and down if it's below 29.999 seconds. You always want to round down since it's From datetime, you need to cheke if you need to decrease a minute:
IF datepart(second, @snapshotdatetime) > 29
OR (datepart(second, @snapshotdatetime) = 29
AND datepart(millisecond, @snapshotdatetime) > 998)
SET @FromDateTime = DATEADD(minute, -1, @FromDateTime)
SET @ToDateTime = DATEADD(minute, 1, @FromDateTime)
然后,在你的 where 子句中,使用这个:
and then, in your where clause, use this:
invoice.snapshot_datetime <= @FromDateTime
AND invoice.snapshot_datetime >= @ToDateTime
这篇关于为什么我不能在 ssrs 中使用日期时间参数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!