i'm putting together a little timesheet system which outputs statistics at the other end. one of my mysql queries is as follows -

SELECT * FROM timesheet WHERE tsdate > $start_date AND tsdate < $end_date

except that I dont want to have to put in a start date and end date, I want it to pick all records with a tsdate within the current month (and eventually another one which picks all records for the previous month) any ideas?

l.

    assuming tsdate is not a unix timestamp:

    SELECT * FROM timesheet WHERE EXTRACT(YEAR_MONTH FROM tsdate) = EXTRACT(YEAR_MONTH FROM NOW())

    for current month

    SELECT * FROM timesheet WHERE EXTRACT(YEAR_MONTH FROM tsdate) = EXTRACT(YEAR_MONTH FROM NOW() - INTERVAL 1 MONTH)

    for previous month

    (not tested, but i like it 😉)

      its a great idea, it looks like it should work, it comes up with no errors but alas it comes up with no results either 🙁 its definately a good starting point towards finding a solution though, thanks! 😃

      l.

        because i've been testing things 😉 and it now works.

        the only thing i've changed to your example was i've removed the now() bit and done that outside of the query

        $rdate = date("Y-m-j");
$year = substr($rdate,0,4);
$mon = substr($rdate,5,2);
$thismonth = $year.$mon;
[code=php]

making the query -

[code=php]SELECT * FROM timesheet WHERE EXTRACT(YEAR_MONTH FROM tsdate) = '$thismonth'

        which produces the results I was after. Many thanks for your help MrHappiness! 🙂

        l.

          and for completeness sake, this is the actual query you just helped me create -

          SELECT tsactivity, SUM(tsduration) FROM int_nat_timesheets WHERE EXTRACT(YEAR_MONTH FROM tsdate) = '$thismonth' GROUP BY tsactivity

            Write a Reply...