Geek Answers Handbook

How to compare two datetime fields but ignore the year?

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

Here's the script:

  • I have two datetime fields called fieldA and fieldB .
  • fieldB will never have a year that is greater than fieldA
  • it is possible that two fields will have the same year.

I need all the records where fieldA > = fieldB , regardless of the year. Just pretend that every field is just a month and a day.

How can i get this? My knowledge of T-SQL date and time functions is accurate at best.

+4
source share
5 answers

You can use the built-in time functions such as DAY and MONTH. eg.

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

Select all rows where either the month of the field is longer or the months are the same, and the time of the field is longer.

+13
source 
 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 need hours, minutes, seconds, you need to expand this to cover cases, although it might be faster to make a great line, delete 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
source 
 SELECT * FROM SOME_TABLE WHERE MONTH(fieldA) > MONTH(fieldB) OR ( MONTH(fieldA) = MONTH(fieldB) AND DAY(fieldA) >= DAY(fieldB) )
+2
source

I would come closer to this in terms of a Julian date, convert each field to a Julian date (the number of days after the first year), and then compare these values.

This may or may not produce the desired results for leap years.

If you were worried about hours, minutes, seconds, etc., you can configure DateDiff functions to calculate the number of hours (or minutes or seconds) from 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)
0
source

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

An example of any date greater than or equal to the current

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

Another 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
0
source

More articles:


All Articles