如何用And/Or逻辑过滤含特殊日期的数据表？

数据老陈

2026-5-21

Hey there! Let's walk through how to filter your date-range records using AND/OR logic, especially accounting for that 1900-01-01 placeholder in the ENDDATE field (which you mentioned should represent a record that's still active, effectively 9999-12-31).

First, let's recap your table structure with sample data (I'll use SQL syntax for clarity):

-- Your table structure
CREATE TABLE TimeSeriesRecords (
    STARTDATE DATETIME,
    ENDDATE DATETIME,
    COMPANYID VARCHAR(10),
    TIMESERIESREFRECID BIGINT
);

-- Sample data you provided
INSERT INTO TimeSeriesRecords VALUES
('2018-01-01 00:00:00.000', '2018-12-31 00:00:00.000', 'OK', 105637207641),
('2019-01-01 00:00:00.000', '1900-01-01 00:00:00.000', 'OK', 105637207641);

Common Filter Scenarios Using AND/OR Logic

1. Get records active on a specific date

If you want to find all records that were valid on a particular date (e.g., 2019-06-01), you'll use AND to ensure the record started before/on the target date, plus OR to handle both active (1900-01-01) and expired records that hadn't ended yet:

DECLARE @TargetDate DATETIME = '2019-06-01';

SELECT *
FROM TimeSeriesRecords
WHERE STARTDATE <= @TargetDate
AND (ENDDATE >= @TargetDate OR ENDDATE = '1900-01-01 00:00:00.000');

This query will return the second record (2019-01-01 start, active) since it was valid on 2019-06-01.

2. Get records overlapping with a date range

To find records that intersect with a specific time frame (e.g., 2018-10-01 to 2019-02-28), use AND to check the record starts before the range ends, and OR to cover both active records and those that ended after the range started:

DECLARE @RangeStart DATETIME = '2018-10-01';
DECLARE @RangeEnd DATETIME = '2019-02-28';

SELECT *
FROM TimeSeriesRecords
WHERE STARTDATE <= @RangeEnd
AND (ENDDATE >= @RangeStart OR ENDDATE = '1900-01-01 00:00:00.000');

This returns both records: the first overlaps with the start of the range, the second overlaps with the end.

3. Get records started before a date AND are still active

If you need records that launched before a cutoff date and are currently active, combine two conditions with AND:

DECLARE @CutoffDate DATETIME = '2019-01-01';

SELECT *
FROM TimeSeriesRecords
WHERE STARTDATE < @CutoffDate
AND ENDDATE = '1900-01-01 00:00:00.000';

No results here, since your active record started exactly on the cutoff date. Adjust < to <= if you want to include that.

4. Get records that either ended before a date OR are still active

Use OR to group two separate conditions: records that expired before a certain point, plus all active records:

SELECT *
FROM TimeSeriesRecords
WHERE ENDDATE < '2019-01-01'
OR ENDDATE = '1900-01-01 00:00:00.000';

This returns both records: the first ended in 2018, the second is active.

Pro Tip: Simplify Filtering with a Calculated End Date

To avoid repeating the ENDDATE = '1900-01-01' check everywhere, you can calculate the actual end date in your query. This makes the logic more intuitive:

DECLARE @TargetDate DATETIME = '2020-01-01';

SELECT 
    STARTDATE,
    CASE WHEN ENDDATE = '1900-01-01' THEN '9999-12-31' ELSE ENDDATE END AS ACTUAL_ENDDATE,
    COMPANYID,
    TIMESERIESREFRECID
FROM TimeSeriesRecords
WHERE STARTDATE <= @TargetDate
AND ACTUAL_ENDDATE >= @TargetDate;

Now you're working with a "real" end date for active records, which makes filtering logic easier to write and read.

内容的提问来源于stack exchange，提问作者SqlKindaGuy