Select dates within the last week

I want to perform an operation in crystal report. I have a db table contains a date column. I want to filter and get the rows having data created in last 开发者_如何学编程week(last sunday to last saturday = 7 days).For example if today is 24th August Wednesday, then I need data from 14th August(Sunday) to 20th August(Saturday).

Basically I want to find 2 dates and filter the date column. Date1 = Date(CurrentDate)-Day(7 + WeekDayinNum(CurrentDate)) ; (Ex:for my example it will be 10) Date2 = Date(CurrentDate)-Day(WeekDayinNum(CurrentDate))

I do not know the Date APIs properly,can anybody help me in this.

This is a common enough date range that CR provides it for you. In your record selection formula, you can just add

{table.date} in LastFullWeek

From CR, "LastFullWeek specifies a range of Date values that includes all dates from Sunday to Saturday of the previous week."

Add this to the record selection formula:

{table.date_field} IN Last7Days

If today is Sunday(1)  you want rows that are between 7 and 1 days old,
If today is Monday(2)  you want rows that are between 8 and 2 days old,
If today is Tuesday(3) you want rows that are between 9 and 3 days old,

etc.

SELECT *
FROM `tablename`
WHERE `somedatefield` >= DATE_SUB(NOW(),INTERVAL (DAYOFWEEK(NOW()) + 6) DAY) 
AND `somedatefield` <= DATE_SUB(NOW(),INTERVAL (DAYOFWEEK(NOW())) DAY)