RESOLVED - Help with a time range filter's syntax please.

[FerdH4]

Good Morning All Syntax Experts,

1. I'm working to create a Report using Cognos v11.2.3. 
2. I have a date/time combo data item named [Event Timestamp].
3. I need to include records with an Event Timestamp between "Yesterday from 7:00 PM through Today at
7:00 AM."

I know how to use the ( _add_days (current_date,-1)) expression to identify "yesterday"...and, now I'm stuck.

Anyone want to help me with the cheat code for the entire filter expression?
 

[cognostechie]

How about making it more flexible instead of hard coding it to work only for yesterday?

Add a DateTime prompt and use a simple filter in the query:

[Event Timestamp] in_range ?pDateTime?

You can also use two DateTime parameters to select the range

[Event Timestamp] >= ?pDateTimeFrom? and [Event Timestamp] <= ?pDateTimeTo?

                        OR

[Event Timestamp] between ?pDateTimeFrom? and ?pDateTimeTo?

The >=, <=, between will work depending on which type of database you have. 'between' usually works very well for SQL Server
 

[dougp]

Using only Cognos functions, this turned out to be much more difficult than I expected:

Code Select Expand

_add_hours(
_make_timestamp(
_year(_add_days(current_date, -1)),
_month(_add_days(current_date, -1)),
_day(_add_days(current_date, -1))
),
19
)
Maybe there's a more direct way, but I'm not seeing it quickly.

[FerdH4]

Thanks to both cognostechie and dougp for the inputs.  I needed a calculated value - one that would not prompt the user to make choice. So, I ended up with the following design on 3/15 which seems to work:

1.   Defined "Yesterday".         cast_date(_add_days ((current_date), -1))
2.   Defined "Today".                cast_date (current_date)
3.   Defined Event Date.        cast_date ([Event Timestamp])
4.   Defined Event Time.        cast_time ([Event Timestamp])
5.   Filtered on multi-part date and time comparisons within a single expression.
      ([Event Date] = [Yesterday]
      AND
      [Event Time] between 19:00:00 and 23:59:59)
      OR
      ([Event Date] = [Today]
      AND
      [Event Time] between 00:00:00 and 07:30:00)


This worked in my instance.  So I'll mark this post as "Resolved!"