Need to simplify [duplicate]

问题:

This question is an exact duplicate of:

• Need better current datetime syntax 1 answer

 WHERE (((tblNewCaseLog.CasePersonAddress) Like "*" & GetCriteria() & "*") AND ((DatePart("yyyy",    
  [DateLog])) Between DatePart("yyyy",Now())-1 And DatePart("yyyy",Now())))
  ORDER BY tblNewCaseLog.DateLog DESC;

--Really need to improve old Access syntax to SQL 2008 for migration. Gracias!

回答1:

WHERE tblNewCaseLog.CasePersonAddress Like '%' + @GetCriteria + '%' 
     AND 
     YEAR([DateLog]) Between YEAR(GETDATE())-1 And YEAR(GETDATE())
ORDER BY tblNewCaseLog.DateLog DESC;

A better performance efficient solution would be

WHERE tblNewCaseLog.CasePersonAddress Like '%' + @GetCriteria + '%' 
     AND 
     [DateLog] >= DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE()) -1, 0) 
     AND 
     [DateLog] <= DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE()) + 1, -1)
ORDER BY tblNewCaseLog.DateLog DESC;