如何使用时间戳作为参数

范·埃斯维尔德

我有一个简单的查询，我经常使用它来获取特定时间段内某个生产线上的所有权重数据。

现在，我想将查询转换为Visual Studio报表，以便其他人可以运行该报表，而不用问我。

第一步，我试图在Management Studio中运行查询以查看其是否有效。

DECLARE @ProductionLineID as int
Set @ProductionLineID = 11
DECLARE @Start as timestamp
Set @Start = '2018-06-29 19:20'
DECLARE @End as timestamp
Set @End = '2018-06-30 19:10' 

SELECT [ProductionLineId]
      ,[Timestamp]
      ,[ActiveRecipe]
      ,[ActualWeight]
      ,[SetWeight]
      ,[SetBoxWeight]
      ,[SetMaxTolerance]
      ,[SetMinTolerance]
      ,[DeviationFromSetWeight]
      ,[AmountOfProductInBox]
      ,[AverageProductWeightPerBox]
      ,[ActualSealTemp]
      ,[ActualCuttingTemp]
      ,[ParametersChanged]
      ,[rejectError]
  FROM [PP_Staging].[NIV].[Packaging]

  where ProductionLineId = @ProductionLineID
  and timestamp between @Start and @End

  order by Timestamp

这导致以下错误

Msg 257, Level 16, State 3, Line 5
Implicit conversion from data type varchar to timestamp is not allowed. Use the CONVERT function to run this query.
Msg 257, Level 16, State 3, Line 7
Implicit conversion from data type varchar to timestamp is not allowed. Use the CONVERT function to run this query.

所以我在相关部分尝试了convert函数：

DECLARE @Start as timestamp
Set @Start = convert(timestamp, '2018-06-29 19:20')
DECLARE @End as timestamp
Set @End = convert(timestamp, '2018-06-30 19:10')

导致

Msg 8115, Level 16, State 2, Line 9
Arithmetic overflow error converting expression to data type datetime.

因此，第一个问题是如何输入/设置此参数，以便可以测试报告？

我知道我的代码可以手动输入，因此我基本上是在寻找一种将手动查询转换为带有变量的报告的方法。了解这些信息后，我可以尝试弄清楚如何设置报告。

工作代码示例：

SELECT [ProductionLineId]
      ,[Timestamp]
      ,[ActiveRecipe]
      ,[ActualWeight]
      ,[SetWeight]
      ,[SetBoxWeight]
      ,[SetMaxTolerance]
      ,[SetMinTolerance]
      ,[DeviationFromSetWeight]
      ,[AmountOfProductInBox]
      ,[AverageProductWeightPerBox]
      ,[ActualSealTemp]
      ,[ActualCuttingTemp]
      ,[ParametersChanged]
      ,[rejectError]
  FROM [PP_Staging].[NIV].[Packaging]

  where ProductionLineId = 11
  and timestamp between '2018-06-29 19:20' and '2018-06-30 19:10'

  order by Timestamp

DB101

检查[PP_Staging]。[NIV]。[Packaging]的定义。我认为您会发现时间戳的数据类型实际上是DATETIME或DATETIME2。然后如下更改变量声明以使其匹配。

DECLARE @ProductionLineID as int
Set @ProductionLineID = 11
DECLARE @Start as DATETIME
Set @Start = '2018-06-29 19:20'
DECLARE @End as DATETIME
Set @End = '2018-06-30 19:10'

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。