mysqlsqldate

MySQL Get a list of dates in month, year


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:

    2014-01-01
    2014-01-02
    2014-01-03
    ...
    2014-01-31

If i do this 'SQL':

SELECT datesInMonth WHERE month = 2 AND year = 2014;

I want to get this list:

    2014-02-01
    2014-02-02
    2014-02-03
    ...
    2014-02-28

If I do this 'SQL':

SELECT datesInMonth WHERE month = 2 AND year = 2016;

I want to get this list:

    2016-02-01
    2016-02-02
    2016-02-03
    ...
    2016-02-29

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


Solution

  • 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 :)