CODEWITHSUNDEEP
sqlsql-server
user16412128
user16412128

Reputation:

How to compare only day and month in sql server

I have a date in a this first field: 2020-07-25 and another date in the second field: 2019-09-21, now I want to compare if the date in the first field is before the date in the second field but only using day and month, I know that using the function MONTH() and DAY() can extract them, but how to compare them after ? This is what I have tried:

SELECT *
FROM Table
WHERE MONTH(FirstDate) < MONTH (SecondDate) AND DAY(FirstDate) < DAY(SecondDate)

Upvotes: 0

Views: 1449

Answers (4)

Zhorov
Zhorov

Reputation: 30003

It seems, that you compare the dates without the year part. In this situation another posible option is to generate and compare new dates using DATEFROMPARTS() and a leap year as year part:

SELECT *
FROM (VALUES
   ('20200725', '20190921'),
   ('20200229', '20160729')
) v (FirstDate, SecondDate)
WHERE 
   DATEFROMPARTS(2000, MONTH(FirstDate), DAY(FirstDate)) <
   DATEFROMPARTS(2000, MONTH(SecondDate), DAY(SecondDate))

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270993

You can also do this by constructing a single value as an integer or string:

where month(firstdate) * 100 + day(firstdate) < month(seconddate) * 100) + day(seconddate)

Or:

where format(firstdate, 'MMdd') < format(secondate, 'MMdd')

Upvotes: 1

maio290
maio290

Reputation: 6742

A date (according to your example) is before another date when:

Month[1] < Month[2]

Month[1] == Month[2] && Day[1] < Day[2]

So this yields in the query:

SELECT *
FROM DATES
WHERE MONTH(datum) < MONTH('2019-09-21')
OR (MONTH(datum) = Month('2019-09-21') AND DAY(datum) < DAY('2019-09-21'))

SQLFiddle

Upvotes: 0

Jiř&#237; Baum
Jiř&#237; Baum

Reputation: 6940

SELECT *
FROM Table
WHERE MONTH(FirstDate) < MONTH (SecondDate)
   OR MONTH(FirstDate) = MONTH (SecondDate) AND DAY(FirstDate) < DAY(SecondDate)

Upvotes: 3

Related Questions