944,173 Members | Top Members by Rank

Web Development > Databases > MySQL > troubble with datediff function from database
Ad:
  • MySQL Discussion Thread
  • Unsolved
  • Views: 3916
  • MySQL RSS
Permalink
Nov 2nd, 2007
0

troubble with datediff function from database

Expand Post »
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 Syntax (Toggle Plain Text)
  1. 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
MySQL Syntax (Toggle Plain Text)
  1. SELECT * DATEDIFF('day', DATE, CURRENTDATE) FROM softwarerepairtable WHERE datediff<= '7' AND complete='yes' ");
  2.  

unfortunatly my limited knowledge of php and sql is holding me back on this. any help guys would be appriacted
Similar Threads
Reputation Points: 10
Solved Threads: 0
Newbie Poster
twodayrule is offline Offline
2 posts
since Nov 2007
Permalink
Nov 2nd, 2007
0

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.
Reputation Points: 11
Solved Threads: 4
Junior Poster in Training
Belrog is offline Offline
71 posts
since Nov 2007
Permalink
Nov 2nd, 2007
0

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.
Last edited by fatihpiristine; Nov 2nd, 2007 at 10:57 am.
Reputation Points: 6
Solved Threads: 19
Posting Whiz in Training
fatihpiristine is offline Offline
283 posts
since Sep 2007
Permalink
Nov 5th, 2007
0

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
Reputation Points: 10
Solved Threads: 0
Newbie Poster
twodayrule is offline Offline
2 posts
since Nov 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in MySQL Forum Timeline: multiple search terms+fulltext search
Next Thread in MySQL Forum Timeline: load data infile - fails to load my db2 del (ascii) file





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC