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

来源：互联网发布：如何封装js 编辑：程序博客网时间：2024/05/07 05:32

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

1 0