SQL Server SQL query to select data between two dates

6fe3ivhb 于 2023-08-02 发布在其他

关注(0)|答案(2)|浏览(113)

I have two dates, effective date '2023-07-03' to expiry date '2024-03-31'. I want to retrieve record on getdate() .

But I am not getting the results from my query:

SELECT 
    [ID],
    [Tariff_ID],
    [Effective_Date],
    [Expiry_Date],
    [Category],
    [Cargo_Class],
    [Wt_Min], [Wt_Max],
    [Charges_PKR], [Charges_Per]
FROM 
    [CARGO].[dbo].[Tariff_Handling]  
WHERE
    Effective_Date >= GETDATE() 
    AND Wt_Min <= 4  
    AND Category = 'AFU' 
    AND Cargo_Class = 'GEN' 
    AND Tariff_ID = 1

My current date is greater than to effective date and less than to expiry date but I'm getting any results

sql-server

来源：https://stackoverflow.com/questions/76699495/sql-query-to-select-data-between-two-dates

2条答案

按热度按时间

nwlls2ji1#

The issue with your query lies in the condition Effective_Date >= GETDATE() . This condition is preventing any records from being retrieved because the effective date of the record '2023-07-03' is not greater than or equal to the current date.

SELECT 
    [ID],
    [Tariff_ID],
    [Effective_Date],
    [Expiry_Date],
    [Category],
    [Cargo_Class],
    [Wt_Min],
    [Wt_Max],
    [Charges_PKR],
    [Charges_Per]
FROM 
    [CARGO].[dbo].[Tariff_Handling]  
WHERE
    Effective_Date <= GETDATE() 
    AND Expiry_Date >= GETDATE()
    AND Wt_Min <= 4  
    AND Category = 'AFU' 
    AND Cargo_Class = 'GEN' 
    AND Tariff_ID = 1

赞(0）回复(0）举报 2023-08-02

5cg8jx4n2#

There where clause needs to be:

WHERE GETDATE() >= Effective_Date
AND   GETDATE() <  DATEADD(DAY, 1, Expiry_Date)

It'll then match an expiry date of 2024-03-31 when current time is 2024-03-31 23:59:59.999

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server SQL query to select data between two dates

2条答案

相关问题

热门标签

最新问答