Time Intervals & Schedules

[Former Member]

Hi,

Can we apply a time Interval & schedule to a sql query? I have defined a time interval and am using that to query a DateTime field in my table. Its not taking the time Interval for the selected Start & End dates and hence the results are not per the time interval selected... Any ideas?

Thanks

[Former Member]

The important configuration step here is to either:

- Assign a Date Column in the SQL Query details tab (if using Query mode)

- Use the [SD] and [ED] parameters in a fixed query tab (if using FixedQuery mode)

- Rick

[abesh]

This is the Data in My SQL table :

Name Age DateTime

-

---

Abesh 26 2006-12-15 07:00:00.000

Partha 27 2006-12-16 07:00:00.000

Dipankar 27 2006-12-17 07:00:00.000

Pradip 25 2006-12-18 07:00:00.000

Rahul 24 2006-12-19 07:00:00.000

Amit 29 2006-12-20 07:00:00.000

-

---

The Database Fields are :

Name : Char(10)

Age : Int

DateTime : Datetime

Query Template : Date Range Tab

-

---

In my Query Template I set the Start Date as : 12/16/2006 07:00:00 and the End Date as : 12/19/2006 07:00:00, Date Format : MM/dd/yyyy HH:mm:ss.

Query Template : SQL Query Details Tab

-

---

Selected Table : MyTable

Selected Columns : Name, Age, DateTime

Date Column : MyTable.DateTime

Query Output

-

---

Name Age DateTime

=================================

Partha 27.00 12/16/2006 07:00:00

Dipankar 27.00 12/17/2006 07:00:00

Pradip 25.00 12/18/2006 07:00:00

Rahul 24.00 12/19/2006 07:00:00

Hope this solves your query.

[Former Member]

The time in My time interval is 07:00:00, duration is set as 12hrs. Format is set as

MM/dd/yyyy HH:mm:ss

In my query template : the Start & End date format is MM/dd/yyyy HH:mm:ss

I have also applied a Date/Time column from the table(whose format is MM/dd/yyyy hh:mm:ss a) to the "Date column" parameter

When I test the query, it says "Unable to parse start or end dates, Invalid date formats.

Then I changed the time interval and the Start/End date formats to MM/dd/yyyy hh:mm:ss a (to match the format in the table). Yet the same error...

Applying a schedule to this time interval did not make any diffrence... Any ideas!!!

[Former Member]

I assume this is a SQL Query (not Tag or Alarm) using the Query Mode, not FixedQuery. In the Filter Expression, are you filtering on your DateTime column like:

...
where mytable.datetimecolumn between [SD] and [ED]
...

To create a "time-sensitive" query, you must use the [SD] and [ED] parameters in the query.

[Former Member]

If you created a Time Interval, have you associated that to a Schedule? Specify both the Schedule and the Time Interval in your query template.

[abesh]

What is the format of the DateTime field in your database Table ?

xMII or xml most probably uses the YYYY-DD-MMThh:mm:ss format for eg : 2006-12-19T06:23:49. Please note that here, T is just the separator.