How can I compare two datetime fields but ignore the year?

Question

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

Here's the scenario:

• I have two datetime fields called fieldA and fieldB.
• fieldB will never have a year value that's greater than the year of 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.

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

Answer 1

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)

Answer 2

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

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.

Answer 3

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

Answer 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.

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.

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)