jonmorris

Timestep invalid

Discussion created by jonmorris on Feb 18, 2010
Latest reply on Oct 26, 2015 by JimmyVivas

When I query the interp2 table with no timestep specified, the query works fine and returns the default timestep of 1h.  When I try to specifiy the timestep as '1m' to get minute data, I get the following error:

Msg 241, Level 16, State 1, Line 2

Conversion failed when converting datetime from character string.

Here is the query which generated the error:

SELECT time, value FROM [PI-SERVER].[piarchive]..[piinterp2]
WHERE tag ='AC_75216.ha'andtimebetween'15-Feb-10'and'16-Feb-10'and timestep ='1m'

I thought that the timestep needed to be in a time format so I tried this syntax:

SELECT time, value FROM [PI-SERVER].[piarchive]..[piinterp2] 
WHERE tag ='AC_75216.ha'andtimebetween'15-Feb-10'and'16-Feb-10'and timestep ='00:01'

Which returned this error message:

OLE DB provider "PIOLEDB" for linked server "PI-SERVER" returned message "Query into 'piarchive..piinterp2 Tbl1002' table is invalid. 'timestep' column must be set to at most one value.".

Msg 7320, Level 16, State 2, Line 2

Cannot execute the query "SELECT "Tbl1002"."time" "Col1005","Tbl1002"."value" "Col1006" FROM "piarchive"."piinterp2" "Tbl1002" WHERE "Tbl1002"."tag"='AC_75216.ha' AND "Tbl1002"."time">='2010-02-15 00:00:00.000' AND "Tbl1002"."time"<='2010-02-16 00:00:00.000' AND "Tbl1002"."timestep"='1900-01-01 00:01:00.000'" against OLE DB provider "PIOLEDB" for linked server "PI-SERVER".

I've tried different syntax with no success.  Any suggestions?

Outcomes