SQL查询，选择两个日期之间的日期

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query.

我有一个start_date和end_date。我想在这两个日期之间找到日期。谁能帮我指出我查询中的错误吗?

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

Here Date is a datetime variable.

这里Date是一个datetime变量。

19 个解决方案

#1

you should put those two dates between single quotes like..

你应该把这两个日期放在单引号之间。

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

or can use

或者可以使用

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

#2

Since a datetime without a specified time segment will have a value of date 00:00:00.000, if you want to be sure you get all the dates in your range, you must either supply the time for your ending date or increase your ending date and use <.

由于没有指定时间段的datetime将具有日期为00: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'

DO NOT use the following, as it could return some records from 2011/02/28 if their times are 00:00:00.000.

不要使用以下内容，因为如果某些记录的时间是00:00: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'

#3

Try this:

试试这个:

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

The date values need to be typed as strings.

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

To ensure future-proofing your query for SQL Server 2008 and higher, Date should be escaped because it's a reserved word in later versions.

为了确保SQL Server 2008和更高版本的查询具有未来验证性，应该转义Date，因为它是后续版本中的保留词。

Bear in mind that the dates without times take midnight as their defaults, so you may not have the correct value there.

记住，没有时间的日期以午夜为默认值，因此您可能没有正确的值。

#4

select * from table_name where col_Date between '2011/02/25' 
AND DATEADD(s,-1,DATEADD(d,1,'2011/02/27'))

Here, first add a day to the current endDate, it will be 2011-02-28 00:00:00, then you subtract one second to make the end date 2011-02-27 23:59:59. By doing this, you can get all the dates between the given intervals.

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

output:
2011/02/25
2011/02/26
2011/02/27

#5

This query stands good for fetching the values between current date and its next 3 dates

这个查询可以在当前日期和下一个日期之间获取值

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

This will eventually add extra 3 days of buffer to the current date.

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

#6

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

-- if data type is different

——如果数据类型不同

#7

This is very old, but given a lot of experiences I have had with dates, you might want to consider this: People use different regional settings, as such, some people (and some databases/computers, depending on regional settings) may read this date 11/12/2016 as 11th Dec 2016 or Nov 12, 2016. Even more, 16/11/12 supplied to MySQL database will be internally converted to 12 Nov 2016, while Access database running on a UK regional setting computer will interpret and store it as 16th Nov 2012.

这是非常古老的，但鉴于我有很多与日期相关的经验，你可能需要考虑一下:人们使用不同的区域设置，因此，一些人(以及一些数据库/计算机，取决于区域设置)可能会在2016年12月11日或2016年11月12日阅读这个日期。此外，提供给MySQL数据库的16/11/12将在内部转换为2016年11月12日，而运行在英国区域设置计算机上的Access数据库将解释并存储为2012年11月16日。

Therefore, I made it my policy to be explicit whenever I am going to interact with dates and databases. So I always supply my queries and programming codes as follows:

因此，每当我要与日期和数据库进行交互时，我的策略是明确显示。所以我总是提供我的查询和编程代码如下:

SELECT FirstName FROM Students WHERE DoB >= '11 Dec 2016';

Note also that Access will accept the #, thus:

还要注意，Access将接受#，因此:

SELECT FirstName FROM Students WHERE DoB >= #11 Dec 2016#;

but MS SQL server will not, so I always use " ' " as above, which both databases accept.

但是SQL server不会，所以我总是使用“'”，这两个数据库都接受。

And when getting that date from a variable in code, I always convert the result to string as follows:

当在代码中从变量中获取日期时，我总是将结果转换为字符串:

"SELECT FirstName FROM Students WHERE DoB >= " & myDate.ToString("d MMM yyyy")

I am writing this because I know sometimes some programmers may not be keen enough to detect the inherent conversion. There will be no error for dates < 13, just different results!

我之所以写这篇文章，是因为我知道，有时有些程序员可能不太热衷于检测固有的转换。日期< 13没有错误，只是不同的结果!

As for the question asked, add one day to the last date and make the comparison as follows:

对于所问的问题，在最后一天增加一天，并做如下比较:

dated >= '11 Nov 2016' AND dated < '15 Nov 2016' 

#8

Try putting the dates between # # for example:

试着把日期放在# #之间，例如:

#2013/4/4# and #2013/4/20#

It worked for me.

它为我工作。

--- EDIT --- I received a notification that I lost two reputation points because somebody down-voted this answer. Please, don't just down-vote if the answer doesn't work for You. Ask for further info/help in the comments, or check other solutions.

--- --- -编辑---我收到了一个通知，我失去了两个声望点，因为有人拒绝了这个答案。如果答案对你不起作用，请不要直接投反对票。在评论中询问更多的信息/帮助，或者检查其他的解决方案。

I don't care about reputation points - I just say that down-votes are not made for that.

我不关心名誉点数——我只是说，低票并不是为此而产生的。

#9

select Date,TotalAllowance 
from Calculation 
where EmployeeId=1
  and convert(varchar(10),Date,111) between '2011/02/25' and '2011/02/27'

#10

best query for the select date between current date and back three days:

从当前日期到前三天的选择日期的最佳查询:

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

best query for the select date between current date and next three days:

从当前日期到未来三天的选择日期的最佳查询:

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

#11

if its date in 24 hours and start in morning and end in the night should add something like :

如果它的日期是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'

#12

Check below Examples: Both working and Non-Working.

检查下面的例子:工作的和不工作的。

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 below is not working:

下面是不工作的:

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

#13

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

#14

we can use between to show two dates data but this will search the whole data and compare so it will make our process slow for huge data, so i suggest everyone to use datediff:

我们可以使用between来显示两个日期数据，但这将搜索整个数据并进行比较，因此这会使我们的过程在处理大型数据时变慢，所以我建议大家使用datediff:

qry = "SELECT * FROM [calender] WHERE datediff(day,'" & dt & "',[date])>=0 and datediff(day,'" & dt2 & "',[date])<=0 "

here calender is the Table, dt as the starting date variable and dt2 is the finishing date variable.

这里日历是表，dt是起始日期变量，dt2是结束日期变量。

#15

I like to use the syntax '1 MonthName 2015' for dates ex:

我喜欢用“2015年1个月的名字”来表示日期:

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

for dates

对日期

#16

I would go for

我就会去

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

The logic being that >= includes the whole start date and < excludes the end date, so we add one unit to the end date. This can adapted for months, for instance:

逻辑是>=包含整个开始日期并 <排除结束日期，因此我们在结束日期添加一个单元。这可以适应几个月，例如:< p>

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

#17

You ca try this SQL

您可以尝试这个SQL

select * from employee where rec_date between '2017-09-01' and '2017-09-11' 

#18

it's better write this way:

最好这样写:

CREATE PROCEDURE dbo.Get_Data_By_Dates
(
    @EmployeeId INT = 1,
    @Start_Date DATE,
    @End_Date Date
)
AS
Select * FROM Calculation  
    where EmployeeId=@EmployeeId AND Test_Date BETWEEN @Start_Date AND @End_Date
RETURN

#19

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');