How do I compare two columns in SQL Server 2016 and if not equal, set them equal based off a conditi-CodePudding

I am creating a query to pull a bunch of information for ID, date, status etc. I have pretty much everything done besides this part. I am trying to compare two variables (which I will call here S_End and P_EndDate) and if P_EndDate is AFTER S_End, then I need to set S_End = P_EndDate.

Both of these variables have dates stored in the format: YYYY/MM/DD HH:MM:SS.

Here is what I have so far for this specific part of my query, just to narrow down to the few entries where this would be applied to:

SELECT status, ID, SStart, S_End, P_EndDate, comment FROM myTable
WHERE SStart IS NOT NULL AND STATUS = 'NOT DONE' AND S_End <> P_EndDate

This works and gets me the few entries I need where P_EndDate is before/after S_End but I am really not sure how to compare/set S_End = P_EndDate. I have tried using an IF statement similar to

IF S_End > P_EndDate 
   BEGIN 
      S_End  = P_EndDate 
   END
ELSE 
   BEGIN
      S_End  = S_End  
   END

but I keep getting syntax errors or a message saying unexpected syntax next to IF. I have tried using CAST to convert both S_End and P_EndDate to just YYYY/MM/DD and then compare but still no luck. I feel this is something easy that I should know but I am not really sure how to go about this.

CodePudding user response：

SQL Variables start with an at "@" sign. These look like columns. If you are wanting to set the columns then...

UPDATE myTable
SET S_End = P_EndDate
WHERE S_End > P_EndDate

If you don't want to change the data, you just want your query to show P_EndDate in those cases, then use a case statement:

SELECT status, ID, SStart, S_End = CASE
   WHEN S_End > P_EndDate THEN P_EndDate
   ELSE S_End
   END, P_EndDate, comment FROM myTable
WHERE SStart IS NOT NULL AND STATUS = 'NOT DONE' AND S_End <> P_EndDate

If you really want variables, then you need to declare them and set their values.