recently i created a system using php and mysql to record faults that the ict technicians could get reported faults out of. so far it does everything i want but some senior members of staff now want the front end to give a report of status, one of the things they wish to see is how many jobs have been completed this week month and year

within my tables is a field date. naturally it has the date submited contained within it. the code i have been using to try and get an output is


the complete feild is another contained within the datebase. i have also tried
unfortunatly my limited knowledge of php and sql is holding me back on this. any help guys would be appriacted

It seems that you are selecting on the wrong check. You are saying that the function datediff should be less or equal to 7. This is not entirely correct. Perhaps you could try testing on the complete datediff result.

this is my own solution: try it. works for me
select * from mytable where
mydate BETWEEN date_format(Now() - INTERVAL 1 DAY,'%e/%c/%Y - %H:%i:%s') AND date_format(Now(),'%e/%c/%Y - %H:%i:%s') ";

this one sorts for 1 day.
if you need week, month, 45, 52 or 665 days

change this
Interval 1 to Interval 45

number is important....

to add dates change minus sign before interval 1
and 1 day would be added automatically.

thank you for the help i have it working and made a nice start to monday morning at work getting it working