AVG pr hour

• kentmelby

  SSC Rookie

  Points: 41

  More actions

  July 18, 2008 at 1:53 am

  #381786

  SELECT

  AVG(VannForing),AVG(Vannstand),AVG(Dosering),AVG(Hastighet),AVG(Silo),AVG(Konduktivitet),AVG(PumpeTrykk),

  AVG(phOpp),AVG(phNed),AVG(VannTemp),AVG(Batteri),AVG(Brensel),AVG(LuftTemp),

  dateadd(hour,0,dateadd(hour, datediff(hour, 0, Tidspunkt), 0)) as dte

  FROM HistorTR WHERE StationID = 1

  AND Tidspunkt BETWEEN '2008314 00:00:00' AND '2008315 23:59:59'

  AND (Tidspunkt < DateAdd(hour, -24, getdate()))

  GROUP BY dateadd(hour,0,dateadd(hour, datediff(hour, 0, Tidspunkt), 0))

  ORDER BY dte

  Dim valg As String = "AVG(VannForing),AVG(Vannstand),AVG(Dosering),AVG(Hastighet),AVG(Silo),AVG(Konduktivitet),AVG(PumpeTrykk), " & _

  "AVG(phOpp),AVG(phNed),AVG(VannTemp),AVG(Batteri),AVG(Brensel),AVG(LuftTemp)"

  sqlStr = "SELECT " & valg & ", dateadd(hour,0,dateadd(hour, datediff(hour, 0, Tidspunkt), 0)) as dte " & _

  " FROM HistorTR WHERE StationID = " + lbl_stasjonsID.Text +

  " AND Tidspunkt BETWEEN '" & yearFrom & "' AND '" & yearTo & "' " & _

  " AND (Tidspunkt < DateAdd(hour, -24, getdate())) " & _

  " group by dateadd(hour,0,dateadd(hour, datediff(hour, 0, Tidspunkt), 0)) " & _

  " ORDER BY dte"

  Conversion failed when converting datetime from character string.

  this queri run ok widhout BETWEEN yearFrom / yearTo, is there anyone that can tell me where BETWEEN statment shoud be or what wrong ?

  Tidspunkt = Date and Time

• Andras Belokosztolszki

  SSC-Insane

  Points: 21971

  More actions

  July 18, 2008 at 2:14 am

  #844848

  In the "between" part use valid datatime values. e.g.:

  cast ('2008/3/14 00:00:00' as datetime)

  AND Tidspunkt BETWEEN CAST('2008/3/14 00:00:00' AS DATETIME)

  AND CAST('2008/3/15 23:59:59' AS DATETIME)

  Regards,

  Andras

  ---

  Andras Belokosztolszki, MCPD, PhD
  GoldenGate Software