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

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