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

SQL query to select dates between two dates

提问by Neeraj

I have a start_dateand 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 Dateis a datetimevariable.

这里Date有一个datetime变量。

回答by Deepak

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'

keep in mind that the first date is inclusive, but the second is exclusive, as it effectively is '2011/02/27 00:00:00'

请记住，第一个日期是包含的，但第二个是不包含的，因为它实际上是 '2011/02/27 00:00:00'

回答by WelshDragon

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

由于没有指定时间段的日期时间的值为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'

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.000，它可能会返回 2011/02/28 的一些记录。

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

回答by WelshDragon

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, Dateshould 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.

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

回答by Chandra Prakash

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.

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

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

回答by hamze shoae

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

-- if data type is different

-- 如果数据类型不同

回答by Vinit Kadkol

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

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

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 天缓冲。

回答by Hannington Mambo

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.

这是非常古老的，但鉴于我在日期方面的很多经验，您可能需要考虑这一点：人们使用不同的区域设置，因此，某些人（以及某些数据库/计算机，取决于区域设置）可能会阅读此内容日期 11/12/2016 为 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.

但 MS SQL 服务器不会，所以我总是使用“'”，如上所述，两个数据库都接受。

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' 

回答by njtd

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

回答by Aleksandar

Try putting the dates between # # for example:

尝试将日期放在 # # 之间，例如：

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

It worked for me.

它对我有用。

回答by Sheryar Nizar

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'