如何在SQL Server中获取无视日期的时分段数据（含分钟）

Got it, let's solve this problem. You need to fetch all records from TestTable where the time component of the DBTimestamp (DateTime column) falls between 3:35 AM and 4:30 AM—regardless of which date the timestamp falls on. Here are three straightforward, reliable approaches for SQL Server:

Approach 1: Break Down Hour and Minute with DATEPART #

This method checks the hour and minute components separately to cover the full time range explicitly:

SELECT *
FROM TestTable
WHERE 
    -- Match times from 3:35 AM to 3:59:59 AM
    (DATEPART(hour, DBTimestamp) = 3 AND DATEPART(minute, DBTimestamp) >= 35)
    -- Match times from 4:00 AM to 4:30:00 AM
    OR (DATEPART(hour, DBTimestamp) = 4 AND DATEPART(minute, DBTimestamp) <= 30)

It’s easy to adjust if you need to tweak the minute thresholds later, and the logic is clear at a glance.

Approach 2: Convert Time to Total Minutes #

By calculating the total number of minutes since midnight, you can simplify the range comparison to a single BETWEEN clause:

SELECT *
FROM TestTable
WHERE 
    DATEPART(hour, DBTimestamp) * 60 + DATEPART(minute, DBTimestamp) 
    BETWEEN 215 AND 270 -- 3*60+35 = 215, 4*60+30 = 270

This is a concise way to handle time ranges, especially useful if you’re working with larger hour/minute values or need to do arithmetic on the time component.

Approach 3: Convert to Time Type for Direct Comparison #

SQL Server lets you convert a DateTime value to a time type, which isolates just the time component. You can then compare this directly to time strings for the most readable option:

SELECT *
FROM TestTable
WHERE 
    CONVERT(time, DBTimestamp) BETWEEN '03:35:00' AND '04:30:00'

Note that this includes seconds and milliseconds, so a timestamp like 2024-01-01 04:30:01.000 won’t be included—this aligns perfectly with your requirement of "3时35分至4时30分之间".

Performance Tip #

If your TestTable has a large volume of data, keep in mind that applying functions directly to DBTimestamp (like DATEPART or CONVERT) can prevent SQL Server from using indexes on that column. For better performance, consider adding a persisted computed column that stores the time component, then create an index on that column.

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