CODEWITHSUNDEEP
sqlsql-server
Mario
Mario

Reputation: 23

SQL Query "time" Reference

I've got the below query in MS SQL Server Management Studio:

SELECT t1.time, value, annotations
FROM PI.piarchive..picomp2 t1
WHERE tag = 'sinusoid'
      AND t1.time >= 't'
      AND annotated = 1

Unfortunately, when I try to run the query, the below error is returned: "Conversion failed when converting date and/or time from character string."

That tells me that it's trying to use the SQL in-built time reference, but preventing me from referring to the "time" attribute in the system table "PI.piarchive..picomp2".

Are you able to advise what changes in syntax I need to make to change the behaviour during query execution so it can query the "time" attribute in the "PI.piarchive..picomp2" table?

EDITED

The "time" attribute is of DateTime type, but since this is a historian I am querying via OLEDB, the reference of 't' (what I am trying to compare with) is a valid value as 't' refers to today.

Upvotes: 2

Views: 278

Answers (1)

Igor Borisenko
Igor Borisenko

Reputation: 3866

As it's said in the error message you are trying to compare value of data type datetime and character string. Of course, that's not aloud. How can you compare for example word 'ostrich' and current date? Which one is bigger or less?

You can compare t1.time with current date this way (SQL Server 2008+): 

t1.time >= CAST(GETDATE() as date)

Upvotes: 1

Related Questions