mysql 取年月日_MySQL获取月,日的日期列表

我正在做一些报告,我希望在特定月份获取所有日期,例如2014年1月,当我执行此“SQL”时：

SELECT datesInMonth WHERE month = 1 AND year = 2014;

我想得到这个清单：

2014-01-01

2014-01-02

2014-01-03

...

2014-01-31

如果我这样做’SQL’：

SELECT datesInMonth WHERE month = 2 AND year = 2014;

我想得到这个清单：

2014-02-01

2014-02-02

2014-02-03

...

2014-02-28

如果我这样做’SQL’：

SELECT datesInMonth WHERE month = 2 AND year = 2016;

我想得到这个清单：

2016-02-01

2016-02-02

2016-02-03

...

2016-02-29

这是可能的,还是我应该用自己的表来确定未来100年的日期:)

解决方法:

这是这个问题的mysql / java解决方案.

创建表语句：

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代码：

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");

}

}

我使用这个sql获取日期列表：

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

希望有人用它:)

标签：mysql,date,sql

来源： https://codeday.me/bug/20190708/1406241.html