我有一个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 
  * 
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

其他回答

由于没有指定时间段的datetime将具有date 00:00:00.000的值,如果您希望确保获得范围内的所有日期,则必须为结束日期提供时间,或者增加结束日期并使用<。

select Date,TotalAllowance from Calculation where EmployeeId=1 
and Date between '2011/02/25' and '2011/02/27 23:59:59.999'

OR

select Date,TotalAllowance from Calculation where EmployeeId=1 
and Date >= '2011/02/25' and Date < '2011/02/28'

OR

select Date,TotalAllowance from Calculation where EmployeeId=1 
and Date >= '2011/02/25' and Date <= '2011/02/27 23:59:59.999'

不要使用下面的语句,因为如果它们的时间是00:00:00.000,它可能会返回一些从2011/02/28开始的记录。

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

我喜欢使用1 MonthName 2015语法来表示日期:

   WHERE aa.AuditDate>='1 September 2015'
     AND aa.AuditDate<='30 September 2015'

对日期

SELECT Date, TotalAllowance  
FROM Calculation  
WHERE EmployeeId = 1 
  AND Date BETWEEN to_date('2011/02/25','yyyy-mm-dd') 
               AND to_date ('2011/02/27','yyyy-mm-dd');
select * from table_name where col_Date between '2011/02/25' 
AND DATEADD(s,-1,DATEADD(d,1,'2011/02/27'))

这里,首先向当前的endDate添加一天,它将是2011-02-28 00:00:00,然后减去一秒,使结束日期为2011-02-27 23:59:59。通过这样做,您可以获得给定间隔之间的所有日期。

output:
2011/02/25
2011/02/26
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**