Funny Bug? within MSSQL Select

[bot1q]

Hey guys,

can someone explain me that "bug?" which is growing from 8.2 --> current version.

SELECT MAX(VALUE_TIME)

FROM MyEpicTableDataAnonym

result = 2019-10.12 .....

and 

SELECT MAX(VALUE_TIME) AS EPICMAXTIME

FROM MyEpicTableDataAnonym

result = 2019-10.03 ..

results me in two different answers ...

[bot1q]

No ideas?

[eliotlandrum]

I'm afraid you'll need to provide more context to understand what you're asking.

[bot1q]

If i do an SELECT without "AS MyFanceValue" I receive  a different result then when i use "AS" in the select statement

[bot1q]

SELECT MAX(VALUE_TIME)    // THIS IS ANONYM

FROM MyEpicTableDataAnonym

result = 2019-10.12 .....

and 

SELECT MAX(VALUE_TIME) AS EPICMAXTIME //THIS IS NOT ANONYM

FROM MyEpicTableDataAnonym

result = 2019-10.03 ..

results me in two different answers on the same data ...

[eliotlandrum]

Can you show some of the data and the type of field that VALUE_TIME is?

[bot1q]

That is data independent..

It is a MS -SQL Database and the type is Datatime 

[eliotlandrum]

I would like to be able to help you, but I really need more information than you're providing. Screenshots of table configuration and sample data would be extremely helpful. You haven't provided enough information for me to be able to replicate this on my own system. The result you showed is not a SQL datetime format (it has a period after the month, which is not standard and doesn't show a time), so you're modifying the results before sharing them here.

[bot1q]

SELECT  MAX(TIME)
FROM
[X].[Y].DATA

Where type of  DATA = datetypeoffset(7) // Database is MSSQL

Example of data: 2018-11-07 00:10:00.0000000 +01:00

[Constantine]

Hello @bot1q,

Wondering whether it's a ThingWorx issue, or MS SQL... Does it work correctly when you execute the same in another SQL client, e.g. sqlcmd?

/ Constantine

[bot1q]

Directly in MSSQL query it works Well.

[Constantine]

Could also be a JDBC driver bug, make sure you use the latest version.

[slangley]

Hi @bot1q.

Please let us know if you found a solution for your issue.  If one of the previous posts helped to solve it, please mark the appropriate one as the Accepted Solution.  Otherwise, please post the solution you found and mark that as the Accepted Solution for the benefit of others with the same issue.

Regards.

--Sharon