Case expression - comparing dates-CodePudding

I am using the query below, if the deadline column (eh.Deadline) < today (getdate) then returns 1, else 0.

(case when he.Deadline < CONVERT(VARCHAR(10),GETDATE(),111) then 1 else 0 end) Deadline

Everything works fine, but I need to change it so that only the month and the year (not the day), are compared.

Example, if Deadline=10/01/2022 and today=11/01/2022

My query returns 1 (10/01/2022 < 11/01/2022)
I would like to compare only the month and the year - I would like it to return 0 as MM/YYYY it's the same (01/2022 = 01/2022)

EDIT:

The column he.Deadline has the following format: DD/MM/YYYY HH/MM and this could not be compared to GETDATE and to solve that I had to use CONVERT(VARCHAR(10),GETDATE(),111) instead of GETDATE

The above query is used for a dashboard I created, and I wouldn't want to change anything in the database.

All I want is to compare MM/YYYY instead of DD/MM/YYYY

Any ideas please?

Thank you very much!

CodePudding user response：

Firstly you need to ensure you are comparing a datetime/date with a datetime/date not a string with a datetime, nor a string with a string, as the comparisons are different all different, and your desired result is comparing a date.

Then you should be storing your data in the correct datatype, which for a datetime is a datetime2. Storing your Deadline as a string is going to cause you pain and trouble for the life of the project and will perform badly.

However with the situation as it is, you need to first convert your Deadline column correctly into a date value, and then secondly one way to solve your actual problem is to compare to the first of the current month as follows:

select
    -- Convert to a date datatype in order to be able to correctly compare to another date
    convert(date,substring(he.Deadline,1,10),103) -- Ensure dd/mm/yyyy as opposed to mm/dd/yyyy
    <
    -- Calculate the first of the month and check whether the Deadline is before then
    convert(date,dateadd(day, -1*(datepart(day,getdate())-1), getdate())) then 1 else 0 end Deadline
from (
  values ('10/01/2022 12/30')
) he (Deadline)

CodePudding user response：

you could do (case when EOMONTH(he.Deadline) < EOMONTH(GETDATE()) then 1 else 0 end) Deadline

set the date to last day of the month for both values before comparing them so only the month/year will matter in the end

edit: as long as Deadline is a date... if it isn't then you have to convert it to date before calling EOMONTH on it