0

Good afternoon,

I'm trying to formulate what should be a simple query that will create a result set of ALL records from a table that have a date that is in the previous month...

Basically something like this one that I use that gets any records prior to the current week, only for the month...

    UPDATE trans_earnings
    SET status = 'P'
    WHERE  status = 'E'
    AND YEARWEEK(earned_date, 0) < YEARWEEK(NOW(), 0)
    AND abbrev = 'ABC'

This would be used in a script that is run once each month, so it would basically effect all records from the previous month.

Any Suggestions?

Thanks in advance
Douglas

1
Contributor
1
Reply
8
Views
3 Years
Discussion Span
Last Post by showman13
0

OK, so I finally figured it out...
Thank Goodness for Google searches... Only took 25+ searches to find it.

    UPDATE trans_earnings
    SET status = 'P'
    WHERE  status = 'E'
    AND earned_date < DATE_FORMAT(NOW(),'%Y-%m-01 00:00:00')
    AND abbrev = 'ABC'

So, no matter what day of the month the CRON job is run, it will get the same results... Every 'E'arned record prior to the current month with the abbrev of 'ABC', will be marked to be 'P'rocessed.

And it works GREAT.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.