您好,我想知道的是在web开发中查询当前一个星期的信息或当前一个月信息是如何实现的。就是查询this week和this month的信息,具体的说就是如何用查询语句实现它
解决方案 »
- tomcat 项目可以不放在ROOT下吗
- String s=new String(s1.getBytes("ISO-8859-1"),"GB2312")问题
- tomcat web.xml 中 <param-value>classpath*:spring/*.xml</param-value> classPath*的定义路径是什么
- 新手提问:向hashMap里添加数据的问题
- 晕,强烈建议!!!
- [求助]查询数据库时碰到一个诡异的错
- 如何显示指定长度的字符串??
- JSP中如何获取当前窗口名字?
- jsp在线等 解决就加分
- 一个Sqlserver 2000 for Jdbc数据源的问题(第四种类型的jdbc驱动程序)
- 在myEclipse中ssh整合开发时起tomcat时出现了下面的问题 求救
- JSP课程作业
根据数据库的不同,也可以使用数据库自带的函数来实现。例如mysql: select * from table where month(时间字段名)=month(now())
一天 "select * from T_news where datediff(day,addtime,getdate())=0";三天 "select * from T_news where datediff(day,addtime,getdate())<= 2 and datediff(day,addtime,getdate())>= 0";
一周 "SELECT * FROM T_news WHERE (DATEPART(wk, addtime) = DATEPART(wk, GETDATE())) AND (DATEPART(yy, addtime) = DATEPART(yy, GETDATE()))";
一月"SELECT * FROM T_news WHERE (DATEPART(yy, addtime) = DATEPART(yy, GETDATE())) AND (DATEPART(mm, addtime) = DATEPART(mm, GETDATE()))";
一季度 "select * from t_news where DATEPART(qq, addtime) = DATEPART(qq, GETDATE()) and DATEPART(yy, addtime) = DATEPART(yy, GETDATE())";其中T_news是表名,addtime是数据库中对于的时间列
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;public class Test2 { public static void main(String[] args) {
// 当月
Calendar c = Calendar.getInstance();
clearTime(c);
c.set(Calendar.DAY_OF_MONTH, 1);
Date currentMonthStart = new Date(c.getTimeInMillis());
c.add(Calendar.MONTH, 1);
Date currentMonthEnd = new Date(c.getTimeInMillis());
// 当周
Calendar c = Calendar.getInstance();
clearTime(c);
c.set(Calendar.DAY_OF_WEEK, Calendar.SUNDAY);
Date currentWeekStart = new Date(c.getTimeInMillis());
c.add(Calendar.DAY_OF_MONTH, 7);
Date currentWeekEnd = new Date(c.getTimeInMillis());
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = null; //
String sql = "SELECT id, name, price FROM sales WHERE sales_date >= ? AND sales_date < ?";
ps = con.prepareStatement(sql);
ps.setDate(1, currentWeekStart);
ps.setDate(2, currentWeekEnd);
...
} catch (SQLException e) {
...
} finally {
...
}
}
private static void clearTime(Calendar c) {
c.set(Calendar.HOUR_OF_DAY, 0);
c.set(Calendar.MINUTE, 0);
c.set(Calendar.SECOND, 0);
c.set(Calendar.MILLISECOND, 0);
}
}