Query to pull data from the past week 1

[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:

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). This is pulling from an Informix database, but forums for Informix are sparse indeed and DB2 is the closest. Can anyone help me set this query up?

Thanks!

 

[MarcLodge]

Hi kaledev,
In DB2 there is a DAYOFWEEK function that returns a number 1 through 7. I don't know Informix, but if I was doing this in DB2 I would look to finding out the day of week of the current date and using it in the SQL

 

[kaledev]

Thank you!,

The WEEKDAY function appears to be the Informix version of DAYOFWEEK. I fixed it with the following:

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