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

I get to dust off my VBScript hat and write some classic ASP to query a SQL Server 2000 database.

我搞砸了我的VBScript帽子并写了一些经典的ASP来查询SQL Server 2000数据库。

Here's the scenario:

这是场景:

I have two datetime fields called fieldA and fieldB.

我有两个名为fieldA和fieldB的日期时间字段。

fieldB will never have a year value that's greater than the year of fieldA

fieldB的年份值永远不会超过fieldA的年份

It is possible the that two fields will have the same year.

这两个字段有可能在同一年。

What I want is all records where fieldA >= fieldB, independent of the year. Just pretend that each field is just a month & day.

我想要的是fieldA> = fieldB的所有记录,与年份无关。只是假装每个领域只是一个月和一天。

How can I get this? My knowledge of T-SQL date/time functions is spotty at best.

我怎么能得到这个?我对T-SQL日期/时间函数的了解充其量只是参差不齐。

5 个解决方案

#1

You may want to use the built in time functions such as DAY and MONTH. e.g.

您可能希望使用内置时间功能,例如DAY和MONTH。例如

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

Selecting all rows where either the fieldA's month is greater or the months are the same and fieldA's day is greater.

选择fieldA的月份较大或月份相同且fieldA的日期更大的所有行。

#2

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))

If you care about hours, minutes, seconds, you'll need to extend this to cover the cases, although it may be faster to cast to a suitable string, remove the year and compare.

如果你关心小时,分钟,秒,你需要扩展它来覆盖这些情况,尽管可以更快地投射到合适的字符串,删除年份并进行比较。

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

#3

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

#4

I would approach this from a Julian date perspective, convert each field into the Julian date (number of days after the first of year), then compare those values.

我会从Julian日期的角度来看待这个,将每个字段转换为Julian日期(第一年之后的天数),然后比较这些值。

This may or may not produce desired results with respect to leap years.

这可能会或可能不会产生关于闰年的期望结果。

If you were worried about hours, minutes, seconds, etc., you could adjust the DateDiff functions to calculate the number of hours (or minutes or seconds) since the beginning of the year.

如果您担心小时,分钟,秒等,可以调整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)

#5

Temp table for testing

临时表进行测试

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

Example of any date greater than or equal to today

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

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

One more example with any day less than today

比今天更少的任何一天的另一个例子

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

#1