troubble with datediff function from database
 

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

mysql_query("SELECT date, datediff('day', date, CURRENTDATE) FROM softwarerepairtable WHERE datediff<= '7' and complete='yes' ");

the complete feild is another contained within the datebase. i have also tried

SELECT * datediff('day', date, CURRENTDATE) FROM softwarerepairtable WHERE datediff<= '7' and complete='yes' ");

unfortunatly my limited knowledge of php and sql is holding me back on this. any help guys would be appriacted

Re: troubble with datediff function from database
 

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.

Re: troubble with datediff function from database
 

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. :)

Re: troubble with datediff function from database
 

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