Query previous week in Informix

kaledev · #1 (**permalink**) 06-08-11, 08:28

I have a report that is running a query to pull data for the previous 7 days, as shown below in the WHERE clause:

Code:

AND rpt.act_ingate_dtm  > TODAY - INTERVAL(7) DAY TO DAY
AND rpt.act_ingate_dtm < TODAY

This is always run on Sunday morning currently....but I need to be able to run it any day of the week and have it always pull from the previous week (Sunday through Saturday). Can anyone help me set this up for Informix specifically?

Thanks!

AlenaR · #2 (**permalink**) 06-15-11, 05:48

What about try something like this:
AND rpt.act_ingate_dtm > TODAY - INTERVAL(7-mod(weekday(today),7)) DAY TO DAY
AND rpt.act_ingate_dtm < TODAY - INTERVAL(mod(weekday(today),7)) DAY TO DAY

Quote:

Originally Posted by kaledev

I have a report that is running a query to pull data for the previous 7 days, as shown below in the WHERE clause:

Code:

AND rpt.act_ingate_dtm  > TODAY - INTERVAL(7) DAY TO DAY
AND rpt.act_ingate_dtm < TODAY

This is always run on Sunday morning currently....but I need to be able to run it any day of the week and have it always pull from the previous week (Sunday through Saturday). Can anyone help me set this up for Informix specifically?

Thanks!

Internet Services

Web Development

Digital Marketing

Consumer Tech