Informix date range issue

building semantic layer
1

I am creating a universe in 4.0 using a derived table. What I need is to bring back last Monday’s data (Actually I need every day last week, but if I can figure one day out, the rest should be easy).

I have found code that works with the Today function, but since the datetimes in the database are based on GMT, I need to subtract eight hours (which I already know how to do). I tried using the Current function, but I get errors.

Any suggestions would be appreciated.

kevlray :us: (BOB member since 2010-06-23)

2

I came up with a solution (not pretty).

ccd.startdatetime >= extend((today - 7) - weekday(today -1), Year to second)- (INTERVAL(8:00) HOUR TO MINUTE)
and ccd.startdatetime < extend((today - 5) - weekday(today -1), Year to second)- (INTERVAL(18:00) HOUR TO MINUTE)

I think the only way I get away with this is because I only am concerned with times between 8:00am and 5:00pm (PST/PDT).

kevlray :us: (BOB member since 2010-06-23)