我有一个start_date和end_date。我想要得到这两个日期之间的日期列表。有人能帮我指出我的查询中的错误吗?

select Date,TotalAllowance 
from Calculation 
where EmployeeId=1
  and Date between 2011/02/25 and 2011/02/27

这里Date是一个datetime变量。


当前回答

我会选择

select Date,TotalAllowance from Calculation where EmployeeId=1
             and Date >= '2011/02/25' and Date < DATEADD(d, 1, '2011/02/27')

逻辑是>=包括整个开始日期,<不包括结束日期,因此我们在结束日期上添加一个单位。这可以适应几个月,例如:

select Date, ... from ...
             where Date >= $start_month_day_1 and Date < DATEADD(m, 1, $end_month_day_1)

其他回答

select * from test 
     where CAST(AddTime as datetime) between '2013/4/4' and '2014/4/4'

——如果数据类型不同

该查询适用于获取当前日期和接下来3个日期之间的值

SELECT * FROM tableName  WHERE columName 
BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 3 DAY)

这最终将为当前日期增加额外3天的缓冲时间。

例子:工作和非工作。

select * from tblUser Where    
convert(varchar(10),CreatedDate,111) between '2015/04/01' and '2016/04/01' //--**Working**

OR

select * from tblUser Where
(CAST(CreatedDate AS DATETIME) between CAST('2015/04/01' AS DATETIME) And CAST('2016/4/30'AS DATETIME)) //--**Working**

OR

select * from tblUser Where
(YEAR(CreatedDate) between YEAR('2015/04/01') And YEAR('2016/4/30')) 
//--**Working**

下面的AND是无效的:

select * from tblUser Where
Convert(Varchar(10),CreatedDate,111) >=  Convert(Varchar(10),'01-01-2015',111) and  Convert(Varchar(10),CreatedDate,111) <= Convert(Varchar(10),'31-12-2015',111) //--**Not Working**


select * from tblUser Where
(Convert(Varchar(10),CreatedDate,111) between Convert(Varchar(10),'01-01-2015',111) And Convert(Varchar(10),'31-12-2015',111)) //--**Not Working**

对当前日期和前三天之间的选择日期的最佳查询:

  select Date,TotalAllowance from Calculation where EmployeeId=1 and Date BETWEEN       
DATE_SUB(CURDATE(), INTERVAL 3 DAY)  AND CURDATE() 

当前日期和未来三天之间的选择日期的最佳查询:

  select Date,TotalAllowance from Calculation where EmployeeId=1 and Date BETWEEN   
   CURDATE()  AND DATE_ADD(CURDATE(), INTERVAL 3 DAY)   
Select 
    * 
from 
    Calculation 
where 
    EmployeeId=1 and Date between #2011/02/25# and #2011/02/27#;