CODEWITHSUNDEEP
mysqlsql
James Walker
James Walker

Reputation: 795

How Can I Get The Next 1st December Date?

I have a date field in my MySQL table and I want to get all rows before the next 1st Of December. So for example the rows I should get back if I run the query today (Nov 2015) would be any date before 1st Dec 2015. If I run the query after 1st Dec 2015 but before 1st December 2016, it should return all rows with a date of before 1st Dec 2016.

Sorry for lack of code, I have literally no idea where to start with this one!

Upvotes: 1

Views: 157

Answers (3)

James Walker
James Walker

Reputation: 795

I did manage my own solution too

SELECT *
FROM table
WHERE date < DATE_ADD( MAKEDATE( IF( MONTH( CURRENT_DATE() ) < 12, YEAR( CURRENT_DATE() ), YEAR( CURRENT_DATE() + INTERVAL 1 YEAR ) ), 1), INTERVAL + 11 MONTH )

Upvotes: 0

HoneyBadger
HoneyBadger

Reputation: 15140

I think you should use something like this:

SELECT      *
FROM        Table T
WHERE       Some_Date < CASE
                            WHEN MONTH(CURDATE()) < 12 THEN STR_TO_DATE('01, 12, ' + YEAR(CURDATE())),'%d,%m,%Y')
                            ELSE STR_TO_DATE('01, 12, ' + YEAR(CURDATE() + 1)),'%d,%m,%Y')
                        END

Disclaimer: I dont have mysql, so I couldn't test it.

Upvotes: 1

agold
agold

Reputation: 6276

I create the limit date based on the current month (using now() to get the current date). If the current month is 12, then the limit is 1-12 of next year (year(now()+1), otherwise the current year.

select YourDate
from  YourTable
where YourDate < 
    case when month(now())=12 then 
        date(concat(convert(year(now()+1), char), '-12-01')) 
    else 
        date( concat(convert(year(now()), char), '-12-01')) 
    end;

Upvotes: 2

Related Questions