0

I want to draw charts from previously saved sensor data from database based on start date and end date parameters provided by user. I show user calendar icon and default start date (=DateAdd("d",-2,Today())) and end date today (=DateAdd("d", 0,Today())).

Calendar icon shows date as 11.6.2016 but in database the date is saved as 2016-06-11 00:00:05.217. If I use the following SQL query nothing is returned but if I use the same in visual studio query designer then I get expected data. What would be the correct syntax for it?

SELECT Timestamp, Value, FieldSensorName
FROM MeasurementTable
WHERE (FieldSensorName IN (@ReportParameterSensorName)) 
  AND (Timestamp > CONVERT(varchar, @ReportParameterStartDate, 121)) 
  AND (Timestamp < CONVERT(varchar, @ReportParameterEndDate, 121))

I am passing selected sensor names (@ReportParameterSensorName) from the UI with multiple selection list box.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
RotatingWheel
  • 1,023
  • 14
  • 27
  • Are your parameters date types? Just use `Timestamp between @ReportParameterStartDate and @ReportParameterEndDate` – alejandro zuleta Jun 14 '16 at 15:16
  • Yes, it is Date/Time type and Timestamp is between start and end. I tested same parameters with query designer and there it is fine. – RotatingWheel Jun 14 '16 at 15:18
  • If your parameters and the Timestamp column are date/datetime type you don't need to convert the types or suit formats. – alejandro zuleta Jun 14 '16 at 15:22
  • Are you trying to pass multiple values in ReportParameterSensorName? FWIW, you really should avoid using reserved words for column names. It makes things more painful than they need to be. – Sean Lange Jun 14 '16 at 15:23
  • @ alejandro zuleta, I tried but did not have luck. – RotatingWheel Jun 14 '16 at 15:49
  • @Sean Lange, I am passing it since user wants to draw one chart for one sensor. User wants to select multiple sensor to draw chart, this why I need to pass an array of sensors. – RotatingWheel Jun 14 '16 at 15:52
  • I figured as much. You can't use a variable like that. It will look for the string literal being in, not as an "array". You will need to use a string splitter here for this to work. http://sqlperformance.com/2012/07/t-sql-queries/split-strings – Sean Lange Jun 14 '16 at 16:32

1 Answers1

0

Your problem could be that you are trying to compare a Timestamp by a greater than with a VARCHAR

AND (Timestamp >  CONVERT(varchar, @ReportParameterStartDate, 121))
AND (Timestamp < CONVERT(varchar, @ReportParameterEndDate, 121))

The CONVERT(varchar,... actually converts the value to a varchar rather than a date. Your parameters are already a date so you shouldn't need to cast or convert them. If Timestamp is a DATETIME field you should just be able to do something like:

AND (Timestamp >  @ReportParameterStartDate)
AND (Timestamp < @ReportParameterEndDate)

Although your parameters will not have hours/minutes/seconds it will still look for everything up after midnight on the Start parameter date and before midnight of the End parameter date. If you intend on the dataset also including the end day you can alter your parameter for end date. 

AND (Timestamp < DATEADD(MS,-3,CAST(CAST(@ReportParameterEndDate + 1 AS DATE) AS DATETIME)))

Is one way I normally get end of day. What it does is add a day to the end date cast it as a date to drop off the hours/minutes/seconds then casts it back to a datetime and deletes 3 milliseconds to get to the highest precision date at the end of day before rounding up to midnight the next day e.g. 2016-06-14 23:59:59.997.

 (FieldSensorName IN (@ReportParameterSensorName))

Could also be an issue depending on what you are passing in the @ReportParameterSensorName variable as it will be treated as a single literal value. So if you are passing a comma separated list or something then you need to lookup how to split the string into a table and use a join rather than an in list. Unless you are okay with some potential for error and just changing to something like

@ReportParameterSensorNameLIKE '%' + FieldSensorName '%'

Which will match any part of the string to the field for sensor name.

Matt
  • 13,833
  • 2
  • 16
  • 28
  • I tried the following before and retested now again but the result is same : SELECT Timestamp, Value, FieldSensorName FROM Measurement WHERE (FieldSensorName IN (@ReportParameterSensorName)) AND (Timestamp > @ReportParameterStartDate) AND (Timestamp < @ReportParameterEndDate) – RotatingWheel Jun 14 '16 at 15:47
  • What value are you passing via @ReportParameterSensorName? Is it a comma seperated list or a single value? Are you sure it has a value when the dataset is being requested? Also what datatype is Timestamp? – Matt Jun 14 '16 at 15:50
  • That parameter goes from UI (selected by user) Following query works fine: SELECT Timestamp, Value, FieldSensorName FROM Measurement WHERE (FieldSensorName IN (@ReportParameterSensorName)) As you see I removed the start and end time parameters. I problem is in Time format, Calendar icon shows 14.6.2016 but database has a format like 2016-06-06 00:00:59.937 Also I am using default value for start time as =DateAdd("d",-2,Today()), not sure if this is mixing up . – RotatingWheel Jun 14 '16 at 17:51
  • That value looks fine. I don't think it is format as much as it is datatypes being your problem. It is likely that either your report parameters for Start and End are not dates or Timestamp is not a date column or they are different types of dates that need to be cast/converted to be compared correctly. You can try to cast everything as a datetime CAST(Start AS DATETIME) CAST(Timestamp AS DATETIME) and see if that will solve your issue. – Matt Jun 14 '16 at 17:57