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

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.