How to use timestamp as parameter

Question

I have a simple query I use frequently to get all weightdata on a certain production line between a specific time period.

Now I want to convert the query I have to a visual studio report so other people can run the report instead of asking me.

As a first step I'm trying to run a query in management studio to see if it works.

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

This results in the following error

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.

So I tried the convert function on the relevant part:

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')

Which results in

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

So first question would be how do I input/set this parameter so I can test the report?

I know my code works if I enter it manually, so I'm basically searching for a way of converting this manual query into a report with variables. Once I have that I can try to figure out how to setup the report.

Working code sample:

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

I can't replicate the error, however, I wonder if it's the use of a non-deterministic date format. Try a deterministic format, such as `yyyy-MM-ddThh:mm:ss` instead. For example `SET @Start = CONVERT(timestamp, '2018-06-29T19:20:00');`. Does that work? — Thom A, Jul 03 '18 at 09:21
You should use datatype 'datetime' instead of 'timestamp': https://stackoverflow.com/questions/7105093/difference-between-datetime-and-timestamp-in-sqlserver — j03p, Jul 03 '18 at 09:26
"The timestamp syntax is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature." Timestamp is NOT intended to hold date/time values, It is NOT comparable to ANSI timestamp. Sql server timestamp is intended for row versioning. See [here](https://learn.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-2017). Like j03p said, you need to use Datetime or datetime2. — HoneyBadger, Jul 03 '18 at 09:32
@Larnu I tried to use this with different variants like including milliseconds or not, didn't work — A. van Esveld, Jul 03 '18 at 09:32
@j03p Thank you for that information, it works when using datetime as type. — A. van Esveld, Jul 03 '18 at 09:35

score 0 · Accepted Answer · answered Jul 03 '18 at 09:42

Check the definition of [PP_Staging].[NIV].[Packaging]. I think you will find that the datatype for timestamp is actually DATETIME or DATETIME2. Then change your variable declarations as below to match.

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'

How to use timestamp as parameter

1 Answers1