如何比较两个日期时间字段而忽略年份?

您可能希望使用内置的时间函数,如日和月。例如

SELECT * from table where
MONTH(fieldA) > MONTH(fieldB) OR(
MONTH(fieldA) = MONTH(fieldB) AND DAY(fieldA) >= DAY(fieldB))

select *
from t
where datepart(month,t.fieldA) >= datepart(month,t.fieldB)
      or (datepart(month,t.fieldA) = datepart(month,t.fieldB)
            and datepart(day,t.fieldA) >= datepart(day,t.fieldB))

如果你关心时、分、秒,你需要将其扩展到案例中,尽管将其转换为合适的字符串可能会更快,删除年份并进行比较。

select *
from t
where substring(convert(varchar,t.fieldA,21),5,20)
         >= substring(convert(varchar,t.fieldB,21),5,20)

SELECT *
FROM SOME_TABLE
WHERE MONTH(fieldA) > MONTH(fieldB)
OR ( MONTH(fieldA) = MONTH(fieldB) AND DAY(fieldA) >= DAY(fieldB) )

如果您担心小时、分钟、秒等,可以调整DateDiff函数以计算年初以来的小时数(或分钟或秒数)。

SELECT *
FROM SOME_Table
WHERE DateDiff(d, '1/01/' + Cast(DatePart(yy, fieldA) AS VarChar(5)), fieldA) >=
      DateDiff(d, '1/01/' + Cast(DatePart(yy, fieldB) AS VarChar(5)), fieldB)

Create table #t (calDate date)
Declare @curDate date  = '2010-01-01'
while @curDate < '2021-01-01'
begin
   insert into #t values (@curDate)
   Set @curDate = dateadd(dd,1,@curDate)
end 

任何大于或等于今天的日期的示例

Declare @testDate date = getdate()
SELECT *
FROM #t
WHERE datediff(dd,dateadd(yy,1900 - year(@testDate),@testDate),dateadd(yy,1900 - year(calDate),calDate)) >= 0

再举一个比今天少一天的例子

Declare @testDate date = getdate()
SELECT *
FROM #t
WHERE datediff(dd,dateadd(yy,1900 - year(@testDate),@testDate),dateadd(yy,1900 - year(calDate),calDate)) < 0