Worked with Brandon on this before, so hopefully he or someone else might be able to help.
The SQL query below occasionally gives me the following error:
(Error converting data type varchar to numeric)
This is due to the fact that, occasionally, the tempF field is populated with NA instead of a value. This occurs when the temperature is not available at the time we sample it, due to station being down, or internet being down etc.
I can either change the SQL string or i can change the database to populate something other than NA whenever it can't get the temp. Any suggestions would be appreciated!
sqlstring1 = "select observation_date, station_id, min(TempF),max(TempF),avg(TempF)from (select dateadd(d, datediff(d, 0, convert(datetime, observation_date, 101)),0) as
observation_date, station_id, cast(TempF as numeric(6,2)) as TempF from weather where
(station_id = '" & querywthr1 & "') and convert(datetime, observation_date, 101) >= '" &
date_range_start & "' and convert(datetime, observation_date, 101) < '" & date_range_end + 1
& "' )a group by observation_date,station_id order by observation_date"