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

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

这里Date是一个datetime变量。


当前回答

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

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

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

其他回答

实际上,为了得到最准确的结果,所有sql日期都应该是yyyy-MM-dd格式。

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

——如果数据类型不同

这对我很有效

SELECT 
  * 
FROM 
  `request_logs` 
WHERE 
  created_at >= "2022-11-30 00:00:00" 
  AND created_at <= "2022-11-30 20:04:50" 
ORDER BY 
  `request_logs`.`id` DESC

Select 
    * 
from 
    Calculation 
where 
    EmployeeId=1 and Date between #2011/02/25# and #2011/02/27#;

例子:工作和非工作。

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**