CODEWITHSUNDEEP
t-sql
Mark Biek
Mark Biek

Reputation: 150779

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

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

Here's the scenario:

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.

Upvotes: 6

Views: 11936

Answers (5)

user3065891
user3065891

Reputation: 1

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

Upvotes: 0

tvanfosson
tvanfosson

Reputation: 532465

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)

Upvotes: 3

Dave_H
Dave_H

Reputation: 6723

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)

Upvotes: 0

mohammedn
mohammedn

Reputation: 2950

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

Upvotes: 2

Vincent Ramdhanie
Vincent Ramdhanie

Reputation: 103135

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.

Upvotes: 15

Related Questions