Time Intervals - one week from today

[Former Member]

I need to create a time interval that picks today's date and then adds 7 more days. Essentially, I want one week's worth of data from today. I will, eventually, have a date picker for the user and pass the date through the query.

I have an interval set up that I thought was easy and straight forward, but when I test this query, I get this error "Unable to parse start or end dates, invalid date format"

How can I do this?

select wrkctr,

endqty,

pkgtype,

payday

from cimuser.fmlbrhis f,

cimuser.mmprdmst m

where f.prodno = m.prodno

and f.payday between [SD] and [ED]

and f.rsncode = '000'

and f.seqno = '0010'

order by wrkctr

[Former Member · Accepted solution]

Did you assign your new time interval to a Time Period Schedule? I think you have to select a Time Period and a Time Schedule in the Query Template to use one or the other. You can do the assigning in the Time Period Schedules page.

[Former Member · Accepted solution]

No, I have not assigned a time period for this. I am wondering if the time periods have to be hard coded meaning if I pick from 03/01/07 to 03/08/07 will I be bounded to that date and I can never change this time period in an irpt?

[jamie_cawley · Accepted solution]

No, they don't need to be hard coded. If you open up the time intervals editor, created a new one with the "Today" selected as the "Day" and "Duration" as "7", "days"

Then in your query select your time period on the Date Range tab.

[Former Member · Accepted solution]

That is exactly what I did.

In my query, I have a criteria of "f.payday between [SD] and [ED]", Is this the problem?

What does this error "Unable to parse start or end dates, invalid date format" mean?

I am thining that I need a different criteria to make this work.

[jamie_cawley · Accepted solution]

The error seems to be related to the date "Format" on the Time Interval. Seems this may be a bug. Delete your time interval and recreate it, make sure you choose the correct format before saving it. If I change the format after saving it, it will give me this error and I can't get rid of it.

Regards,

Jamie

[Former Member · Accepted solution]

I recreated the time interval. I noticed the bug so I made sure to recreate it and then log out and login to make sure my time interval had everything you stated. The Date format in Time Interval is mm/dd/yyyy. I changed this in the query itself and I still got the same error.

The data source is ORACLE and I am wondering if the date comes into xMII as Date and Time even though there is no Time associated with this field. Or could this be a problem because the time interval needs a time when my field does not have one?

[jamie_cawley · Accepted solution]

The bug should be fixed in SR03. In the meantime try using "7" "Days" as the duration on the Date Range. If you still having issue take a look at note 943507 to verify that you have the correct oracle driver installed.

Jamie

[Former Member · Accepted solution]

So your saying this is another xMII bug and I should have the between SD and ED as my criteria?

[jamie_cawley · Accepted solution]

Using "between [SD] and [ED]" should work.

The bug is in the time interval and should be fixed in SR03. In the meantime try using "7" "Days" as the duration on the Date Range tab.

If you still having issue take a look at note 943507 to verify that you have the correct oracle driver installed.

[Former Member · Accepted solution]

Hi,

You can use this like this.

select wrkctr,

endqty,

pkgtype,

payday

from cimuser.fmlbrhis f,

cimuser.mmprdmst m

where f.prodno = m.prodno

and f.payday between '[Param.1]' and '[Param.2]'

and f.rsncode = '000'

and f.seqno = '0010'

order by wrkctr

Call this query template in the BLS.

Go to the Link editor, For the Param.1 Link the funtion datenow. It will pick the current system date. Date now function will give you the date in xml format. Change this by using dateformat( datetime, fromformat, toformat )

datetime - datenow

fromformat - "MM-dd-yyyy hh:mm:ss"

to format - Change this to the required format.

In the Param.2

Link this function

dateadddays( datestart , amount )

date start - today date

amount -7

Try with this ..

Regards,

Kishore

[Former Member]

Can you try f.payday >= [SD] and f.payday <=[ED] once..

Thanks,

Rajesh.