SQL 获取指定月份的所有日期，SQL日期分页

package awu.demo;
import java.util.Date;
import java.util.List;
import awu.util.DateUtil;
public class SQLDemo {

/**
* @param year 年份
* @param month 月份
* @return 生成的sql 语句 如果是当前月份 则返回本月第一天到今天的所有日期
*/
public static String getDateSqlByYearMonth(int year, int month) {
        Date sDate = DateUtil.getMonthFirstDay(DateUtil.getTargetDateOfMonth(year, month));
        String sDateStr = DateUtil.getMonthFirstDayStr(new Date());
        Date eDate = DateUtil.getMonthLastDay(DateUtil.getTargetDateOfMonth(year, month));
        String dateStr = year+"-";
        if(month<10){
            dateStr+="0"+month;
        }else{
            dateStr+=month;
        }
        if(dateStr.equals(DateUtil.getCurDateOfYearmonth())){
            eDate = new Date();
        }
        if((dateStr+"-01").equals(sDateStr)){
            sDate = DateUtil.getLastMonthAfterDay();
        }
        String dateSql = createDateSql(eDate,sDate);
        return dateSql;
    }

/**
* 生成 sql 语句
*/
private static String createDateSql(Date endDate,Date startDate){
        StringBuffer dateStr = new StringBuffer("select xyz.dt from (");
        try {
            List<String> list = DateUtil.getDaysBetweenStartDateAndEndDate(startDate, endDate);
            for(int i=0;i<list.size();i++){
               if(i==0){
                   dateStr.append("select "").append(list.get(i)).append("" as dt");
               }else{
                   dateStr.append(" UNION ALL select "").append(list.get(i)).append("" as dt");
               }
            }
            dateStr.append(" ) xyz");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return dateStr.toString();
    }
}

日期操作类

package awu.util;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
/**
 * 对日期的运算操作
 *
 */
public class DateUtil {
    public static final SimpleDateFormat SDF_MD = new SimpleDateFormat("MM-dd");
    public static final SimpleDateFormat SDF_DATE = new SimpleDateFormat("yyyy-MM-dd");
    public static final SimpleDateFormat SDF_DATE_MONTH = new SimpleDateFormat("yyyy-MM");
    public static final SimpleDateFormat SDF_SECOND_MILLISECOND = new SimpleDateFormat("SSsss");

   /**
* 获取日期所在月份的第一天
* @param date
* @return
*/
public static <T extends Date> T getMonthFirstDay(final T date) {
if(date == null) {
            return null;
        }
final String dateStr = format(date, "yyyy-MM") + "-01";
final Long mill = parseDate(dateStr).getTime();
final T another = (T) date.clone();
another.setTime(mill);
return another;
}
/**
 * 格式化日期
 * @param date
 * @return
 */
public static String getMonthFirstDayStr(Date date){
  Date d = getMonthFirstDay(date);
  return format(date, "yyyy-MM-dd");
}

/**
*获取日期所在月份的最后一天
* @param days
* @return
*/
public static <T extends Date> T  getMonthLastDay(final T date){
if(date == null) {
            return null;
        }
final Calendar c = Calendar.getInstance();
c.setTime(date);
final String dateStr = format(date, "yyyy-MM") + "-" + c.getActualMaximum(Calendar.DAY_OF_MONTH);
final Long mill = parseDate(dateStr).getTime();
final T another = (T) date.clone();
another.setTime(mill);
return another;
}

/**
*
* @param days
* @return
*/获取当前日期
public static String getCurDateOfYearmonth(){
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM");
          return dateFormat.format(new Date());
    }
/**
* 获取上个月最后一天的日期
*/
public static Date getLastMonthAfterDay(){
   Calendar calendar = Calendar.getInstance();
   calendar.set(Calendar.DAY_OF_MONTH, 1);
   calendar.add(Calendar.DATE, -1);
   return calendar.getTime(); 
}

}

测试
package awu.demo;
public class test {
public static void main(String[] args) {
System.out.println(SQLDemo.getDateSqlByYearMonth(2016, 9));

System.out.println(SQLDemo.getDateSqlByYearMonth(2015, 2));
}
}

执行生成的sql结果如图

扩展

根据日期分页

/**
* @param from-当前页 从1开始  第一页从当前日期开始往前推rows天 
* @param rows-每页数据条数
*/
public static String getDateSqlByForPage(int from, int rows) {
String dateSql = "";
Date eDate = new Date();
Date sDate = new Date();
try {
if(from == 1){
sDate = DateUtil.addDayByTarDate(new Date(),-rows);
}else{
sDate = DateUtil.addDayByTarDate(new Date(),-(from)*rows);
eDate = DateUtil.addDayByTarDate(sDate,rows);
}
dateSql = createDateSql(eDate,sDate);
} catch (Exception e) {
e.printStackTrace();
}
        return dateSql;
    }

注意：这里DateUtil中的getDaysBetweenStartDateAndEndDate这个方法改动了一下，主要是这里取得二个日期间的集合是左右包含，按照分页要求所以改成左包含右不包含

具体如下：

/**
     * 获取二个日期之间的的所有日期集合
     * dwzhou@atman.com
     * 2016年6月6日下午2:57:23
     */
    public static List<String> getDaysBetweenStartDateAndEndDate(Date startDate,Date endDate){
        List<String> list = new ArrayList<String>();
        Calendar cd = Calendar.getInstance();
        cd.setTime(startDate);
        while(startDate.getTime()-endDate.getTime()<0){
            startDate = cd.getTime();
            cd.add(Calendar.DATE, 1);
            list.add(SDF_DATE.format(startDate));
        }
        if(list!=null && list.size()>1){
        list = list.subList(0, list.size()-1);
        }
        return list;
   }

执行一下生成的sql语句就可以看到效果了

public static void main(String[] args) {
System.out.println(getDateSqlByForPage(1,20));
System.out.println(getDateSqlByForPage(2,20));
System.out.println(getDateSqlByForPage(3,20));
System.out.println(getDateSqlByForPage(4,20));
}