sql 获取日期区间的数据，如果有就返回区间，如果没有就返回最新的日期区间

kuyz

于 2024-01-02 14:13:47 发布

阅读量617

点赞数 8

文章标签：数据库

本文链接：https://blog.youkuaiyun.com/kuyz1/article/details/135339681

版权

CREATE TABLE [dbo].[YourTableName](
	[StartDate] [datetime] NULL,
	[EndDate] [datetime] NULL
) ON [PRIMARY]
GO


insert into YourTableName values('2023-01-10','2023-01-19')
insert into YourTableName values('2023-01-26','2023-01-27')

declare @date datetime
set @date='2023-03-21'
			SELECT *  
FROM YourTableName   T_A
WHERE   
    -- 如果今天在范围内  
    (startDate <= @date AND endDate >= @date)  
    -- 或者范围没有数据，取enddate距离今天最近的数据  
    OR (NOT EXISTS (  
        SELECT 1  
        FROM YourTableName AS T_A_sub  
        WHERE startDate <= @date AND endDate >=@date
    ) AND T_A.EndDate =(select  max( EndDate) from YourTableName) );