SQL查询在两个日期之间选择日期

我有一个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] between '2011/02/25' and '2011/02/27'

日期值需要作为字符串输入。

为了确保您的SQL Server 2008及更高版本的查询能够适应未来，Date应该被转义，因为在以后的版本中它是一个保留字。

请记住，没有时间的日期将午夜作为默认值，因此这里可能没有正确的值。

2011-02-26 04:36:22

其他回答

例子:工作和非工作。

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

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

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

2016-01-13 07:22:14

如果日期在24小时内，从早上开始，到晚上结束，应该添加如下内容:

declare @Approval_date datetime
set @Approval_date =getdate()
Approval_date between @Approval_date +' 00:00:00.000' and @Approval_date +' 23:59:59.999'

2014-03-25 08:39:04

SELECT CITY, COUNT(EID) OCCURENCES FROM EMP 
WHERE DOB BETWEEN '31-JAN-1900' AND '31-JAN-2900' 
GROUP BY CITY 
HAVING COUNT(EID) > 2;

该查询将查找DOB位于员工指定时间范围内的次数超过2次的城市。

2021-08-01 13:27:50

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 10 [Id]
  ,[Id_parvandeh]
  ,[FirstName]
  ,[LastName]
  ,[RegDate]
  ,[Gilder]
  ,[Nationality]
  ,[Educ]
  ,[PhoneNumber]
  ,[DueInMashhad]

  ,[EzdevajDate]


  ,[MarriageStatus]
  ,[Gender]
  ,[Photo]

  ,[ModifiedOn]
  ,[CreatorIp]
   From
  [dbo].[Socials] where educ >= 3 or EzdevajDate  >= '1992/03/31' and EzdevajDate <= '2019/03/09' and MarriageStatus = 1

2019-09-02 20:06:09