New post
Question
· Dec 8, 2016

[Bug Filed] Can anyone explain this odd query behavior?

#SQL #HealthShare
SELECT MAX(LocalDateTime)
FROM HS_IHE_ATNA_Repository.Aggregation
WHERE LocalDateTime < '2016-12-01'
> 2016-11-30 23:59:59

 That is exactly what I would expect.

SELECT MAX(IndexedDate)
FROM HS_IHE_ATNA_Repository.Aggregation
WHERE IndexedDate < '2016-12-01'
> 2016-12-01 00:00:00

That is NOT expected. 

Discussion (3)0
Log in or sign up to continue
Brendan Bannon · Dec 8, 2016

Scott

This is a bug that ISC Development needs to look at.  I spoke to the developer about it, he understands what is going wrong and will look at options to fix this.

The problem is the format of the data in the index does not match the format of %TimeStamp.  This Property is defined as a %TimeStamp but we are only storing the date part in the index.   If you modify you query to something like this then it should return the correct answer:

SELECT MAX(IndexedDate)
FROM HS_IHE_ATNA_Repository.Aggregation
WHERE DATEPART(sqltimestamp,IndexedDate) < '2016-11-28'

If you would like to track the Prodlog you can open an WRC issue and tell the advisor to come and talk to me about this.

3 0