T_stamp Differences between group type

Archived Topics
1

This is a very strange problem and since you guys are great at solving them, here goes …

I am logging to MSSQL on a different server. I have several groups. So far I have only been querying data from “Historical Groups” and everything is fine. I have some “Standard Groups” that I am now trying to work with and cannot query a record with a specific time. Whenever I do the query returns no rows.

To verify my SQL syntax I ran the same query in SQL Enterprise Manager and it works in some tables but not the others. I looked at each of the tables and the only difference is that the tables that work are from Historical Groups and the ones that do not are Standard Groups.

Both group types have the “Automatically create table” and “Store time/date stamp” checked.

I looked at the tables created in MSSQL and the t_stamp field types are all datetime (8)

As always, I appreciate all the help you provide.

2

How specific is the time that you’re specifying? SQL Server’s timestamps have millisecond resolution. Post the exact query you’re using if you can.

3

I have a dynamic property lastUpdateTime defined as a date with this query

[quote]SELECT MAX(t_stamp)
FROM L1_NDCRollProfile[/quote]
I then use that time to get the newest record.

[quote]SELECT RollProfile
FROM L1_NDCRollProfile
WHERE t_stamp = ‘{Root Container.lastUpdateTime}’[/quote]

I also tried building the date as a string. I tried to use millisecond resolution but I do not know how to format it.

I just did an experiment using ‘>’ and ‘<’ in the query browser and it returns a record.

Two questions:

  1. How do specify the date down the millisecond?
  2. Have I just been lucky so far with the historical groups?
4

Yeah, when you include timestamps like that in queries they only go to second resolution. You could add a intermediary string dynamic property bound to an expression like:

dateFormat({Root Container.lastUpdateTime}, 'yyyy-MM-dd HH:mm:ss.SSS')

And then use that dynamic property in your query.

5

PERFECT!!
Works every time.

I tried this and then gave up.

dateFormat({Root Container.lastUpdateTime}, 'yyyy-MM-dd HH:mm:ss.sss')

Thank you.

6

You were so close! Those date format strings are case-sensitive.

Glad its working,