MySQL Get a list of dates in month, year

Question

I am doing some reports and I want to get all dates in specific month, for example January 2014, and when I do this 'SQL':

SELECT datesInMonth WHERE month = 1 AND year = 2014;

I want to get this list:

If i do this 'SQL':

SELECT datesInMonth WHERE month = 2 AND year = 2014;

I want to get this list:

If I do this 'SQL':

SELECT datesInMonth WHERE month = 2 AND year = 2016;

I want to get this list:

Is this possible, or should I do my own table with dates for the next 100 years :)

There's a clever trick you can do with UNIONs, or you can just handle the business logic at the application level. But your other idea is OK too. A calendar table with dates for even the next 1000 years would be tiny. — Strawberry, Feb 06 '14 at 16:00
Yeah it would solve my problem for the next 100 years, but i wanted a more better solution if exists. — AdrianES, Feb 06 '14 at 16:02
You may need a procedure like the one here. It creates a table, but you might be able to return a resultset instead - http://schimpf.es/generate-a-list-between-two-dates-in-mysql/ — Shiva, Feb 06 '14 at 16:06
@Strawberry Why not u come in 100 years, and we'll see if u have a better comment. — AdrianES, Feb 06 '14 at 16:12

score 2 · Accepted Answer · answered Feb 07 '14 at 11:00

Here is the mysql/java solution for this question.

CREATE TABLE STATMENT:

CREATE TABLE `date_table` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Java code:

public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Database db = new Database("root", "root01", "test-db", "10.10.5.11");
        db.connect();
        int start_year = 2013;
        int end_year = 2100;
        for (int year = start_year; year < end_year; year++) {
            for (int month = 1; month <= 12; month++) {
                int days_in_month = 0;
                switch (month) {
                    case 1: { //January
                        days_in_month = 31;
                        break;
                    }
                    case 2: { //February
                        if (year % 4 == 0) {
                            days_in_month = 29;
                        } else {
                            days_in_month = 28;
                        }
                        break;
                    }
                    case 3: { //March
                        days_in_month = 31;
                        break;
                    }
                    case 4: { //April
                        days_in_month = 30;
                        break;
                    }
                    case 5: { //May
                        days_in_month = 31;
                        break;
                    }
                    case 6: { //June
                        days_in_month = 30;
                        break;
                    }
                    case 7: { //July
                        days_in_month = 31;
                        break;
                    }
                    case 8: { //August
                        days_in_month = 31;
                        break;
                    }
                    case 9: { //September
                        days_in_month = 30;
                        break;
                    }
                    case 10: { //October
                        days_in_month = 31;
                        break;
                    }
                    case 11: { //November
                        days_in_month = 30;
                        break;
                    }
                    case 12: { //December
                        days_in_month = 31;
                        break;
                    }
                }
                for (int day = 1; day <= days_in_month; day++) {
                    db.executeInsert("INSERT INTO date_table (date) VALUES ('" + year + "-" + month + "-" + day + "');");
                }
            }
            System.out.println("YEAR: " + year + " FINISHED");
        }
    }

I get the list of dates using this sql:

SELECT * FROM date_table WHERE YEAR(date) = 2014 AND MONTH(date) = 2;

Hope someone use it :)

score 1 · Answer 2 · edited Feb 06 '14 at 16:05

1

You can use the datetime function in MySQL. In particular, MONTH() and YEAR(). If you pass a date into those functions, it'll return the month or year. For example:

WHERE MONTH(my_date) = 2 and YEAR(my_date) = 2014

If you want to get all of the dates in a month, you can do something like this:

SELECT my_date FROM my_table
WHERE MONTH(my_date) = 2 and YEAR(my_date) = 2014

edited Feb 06 '14 at 16:05

Shiva

20,575
14
82
112

answered Feb 06 '14 at 15:59

Damien Black

5,579
18
24

they can do that using my where statement – Damien Black Feb 06 '14 at 16:02
If I create the date table I will use for example 'SELECT DATE(full_date) WHERE MONTH(full_date) = 2 AND YEAR(full_date) = 2014' but this is my second option. – AdrianES Feb 06 '14 at 16:06

MySQL Get a list of dates in month, year

2 Answers2

Linked