Question

如果必须检索干 the的记录,这一记录应当尽可能靠近我的时间段(YYY-MM),请记住,db(sql服务器)的栏目是DATETIME,因此,我需要加以编排,以便比较一下,这里是我一直在做的:

public Document findByDate(String date) throws GeneralException{
    Document docs = new Document();
    String d1 = date;
    String delimiter = "-";
    String[]temp = d1.split(delimiter);     
    try{
        String sql = "SELECT TOP(1) *  FROM Document  WHERE issueDate >=  " +   temp[1]+ temp[0] +"   AND issuedate <  "+ temp[1]+ temp[0] +"   ORDER BY issueDate DESC ";
        ResultSet rs = this.executeQuery(sql);
        while(rs.next()){
            docs = (Document) this.build(rs);
        }
        if(docs != null){
            return docs;
        } else {
            return docs = null;
        }
    } catch (SQLException ex){
        throw new GeneralException(ex);
    }
}

非常感谢

Answer 1

So what you want is th most recent record in the same or previous month, course we have to bear in mind that the month previous to December was last year...

你们可以想一下一下的是,月与年之间的比较,但如果你不是在YYYY-MM通过,那么你就是在那个月末通过,那么,这只是一次。

Select Top(1) * from Documents Where IssueDate <= ? Order By IssueDate Desc

Where ? is the end of the month

Answer 2

你应该利用“强调”来把论点传给一个问题。这是一种错误易发、数据库和当地独立者,使你的代码容易遭到攻击。相反,使用,并将日期作为java.sql。日期,而不是通过:

String sql = 
    "SELECT TOP(1) * FROM Document WHERE issueDate >= ? AND issuedate < ? ORDER BY issueDate DESC";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setDate(1, date);
stmt.setDate(2, date);
ResultSet rs = stmt.executeQuery();

尽管如此,我看不出您的问询如何能够奏效。数据库中的日期如何缩短或等于某一日期,并超过同一日期? 你们是否应当把日期作为最低约束,你的日期+1个月作为上限?

Answer 3

审判

SELECT TOP(1) *  FROM Document  WHERE convert(date,issueDate) >=   datevalue   AND convert(date,issuedate) <   datevalue    ORDER BY issueDate DESC

友情链接