从两个日期之间的日期范围中选择数据

尝试以下查询以获取范围之间的日期:

SELECT  *
FROM    Product_sales
WHERE   From_date >= '2013-01-03' AND
To_date   <= '2013-01-09'

您应该像比较数值一样在 sql 中比较日期,

SELECT * FROM Product_sales
WHERE From_date >= '2013-01-01' AND To_date <= '2013-01-20'

SELECT * FROM Product_sales
WHERE From_date between '2013-01-03'
AND '2013-01-09'

请试试:

DECLARE @FrmDt DATETIME, @ToDt DATETIME
SELECT @FrmDt='2013-01-03', @ToDt='2013-01-09'


SELECT *
FROM Product_sales
WHERE (@FrmDt BETWEEN From_date AND To_date) OR
(@ToDt BETWEEN From_date AND To_date)

SELECT *
FROM Product_sales
WHERE (
From_date >= '2013-08-19'
AND To_date <= '2013-08-23'
)
OR (
To_date >= '2013-08-19'
AND From_date <= '2013-08-23'
)

就我个人而言，我发现使用“ dd-MMM-yyyy”格式最安全，因为不管服务器上的区域设置如何，数据库服务器都知道您想要什么。否则，您可能会在日期区域设置为 yyyy-dd-mm (无论什么原因)的服务器上遇到问题

因此:

SELECT * FROM Product_sales
WHERE From_date >= '03-Jan-2013'
AND To_date <= '09-Jan-2013'

这对我来说一直很管用; -)

这是在 SQL _ Server _ 2008 R2上进行的

Select *
from Product_sales
where From_date
between '2013-01-03' and '2013-01-09'

正如你所看到的，有两种方法可以完成任务:

• 列出所有可接受的选项
• 排除所有错误的选择

显然，第二种方法要简单得多(只有两种情况对抗四种情况)。

您的 SQL 将类似于:

SELECT * FROM Product_sales
WHERE NOT (From_date > @RangeTill OR To_date < @RangeFrom)

下面是一个查询，查找8月份期间运行的所有产品销售情况

• 查找在8月份期间活动的 Product _ sales
• 包括八月底之前开始的任何事情
• 排除任何在8月1日之前结束的

还添加了一个 case 语句来验证查询

SELECT start_date,
end_date,
CASE
WHEN start_date <= '2015-08-31' THEN 'true'
ELSE 'false'
END AS started_before_end_of_month,
CASE
WHEN NOT end_date <= '2015-08-01' THEN 'true'
ELSE 'false'
END AS did_not_end_before_begining_of_month
FROM   product_sales
WHERE  start_date <= '2015-08-31'
AND end_date >= '2015-08-01'
ORDER  BY start_date;

SELECT NULL
FROM   HRMTable hm(NOLOCK)
WHERE  hm.EmployeeID = 123
AND (
(
CAST(@Fromdate AS date) BETWEEN CAST(hm.FromDate AS date)
AND CAST(hm.ToDate AS date)
)
OR (
CAST(@Todate AS date) BETWEEN CAST(hm.FromDate AS date)
AND CAST(hm.ToDate AS date)
)
)
)

这个查询将帮助您:

select *
from XXXX
where datepart(YYYY,create_date)>=2013
and DATEPART(YYYY,create_date)<=2014

检查这个查询，我创建了这个查询来检查签入日期是否与任何预订日期重叠

SELECT * FROM tbl_ReservedRooms
WHERE NOT ('@checkindate' NOT BETWEEN fromdate AND todate
AND '@checkoutdate'  NOT BETWEEN fromdate AND todate)

这将返回重叠的细节，得到不重叠的细节，然后从查询中删除’非’

SELECT * from Product_sales where
(From_date BETWEEN '2013-01-03'AND '2013-01-09') OR
(To_date BETWEEN '2013-01-03' AND '2013-01-09') OR
(From_date <= '2013-01-03' AND To_date >= '2013-01-09')

你必须考虑到所有的可能性。From _ Date或 到 _ 日期可以在你的日期范围之间，或者记录日期可以覆盖整个范围。

如果 From_date或 To_date之一在日期之间，或者 From_date小于开始日期，而 To_date大于结束日期，则应返回此行。

select *
from table
where
( (table.EndDate > '2013-01-05') and (table.StartDate < '2013-01-07' )  )

您也可以尝试使用以下片段:

select  * from  Product_sales
where  From_date  >= '2013-01-03' and game_date  <= '2013-01-09'

这很简单，使用这个查询查找您想要的。

select * from Product_Sales where From_date<='2018-04-11' and To_date>='2018-04-11'

这很简单，可以使用此查询在两个日期之间的日期范围内查找选择数据

select * from tabblename WHERE (datecolumn BETWEEN '2018-04-01' AND '2018-04-5')

DECLARE @monthfrom int=null,
@yearfrom int=null,
@monthto int=null,
@yearto int=null,
@firstdate DATE=null,
@lastdate DATE=null


SELECT @firstdate=DATEADD(month,@monthfrom-1,DATEADD(year,@yearfrom-1900,0)) /*Setting First Date using From Month & Year*/
SELECT @lastdate= DATEADD(day,-1,DATEADD(month,@monthto,DATEADD(year,@yearto-1900,0)))/*Setting Last Date using From Month & Year*/


SELECT *  FROM tbl_Record
WHERE  (DATEADD(yy, Year - 1900, DATEADD(m, Month - 1, 1 - 1)) BETWEEN CONVERT(DATETIME, @firstdate, 102) AND
CONVERT(DATETIME, @lastdate, 102))

这涵盖了所有条件，您正在寻找。

SELECT * from Product_sales where (From_date <= '2013-01-09' AND To_date >= '2013-01-01')