In Case statement update date not greater than today date-CodePudding

Hi as per current statement i am updating the date in table as per below statement. I want to update the date that will be not more than today date. If the date will be more than today date then it will update with today date.

Update visiting 
set OriginArrvDate
       WHEN DATEPART(weekday,ORDDATE)=6 THEN DATEADD(dd,3,ORDDATE)
       WHEN DATEPART(weekday,ORDDATE)=7 THEN DATEADD(dd,3,ORDDATE)
       WHEN DATEPART(weekday,ORDDATE)=1 THEN DATEADD(dd,2,ORDDATE)
       WHEN DATEPART(weekday,ORDDATE)=2 THEN DATEADD(dd,1,ORDDATE)
       ELSE ORDDATE END
where 
orddate>=CONVERT(DATE,DATEADD(dd,-100,getdate()))

In above script i am trying that when after calculating the date in ( DATEADD(dd,3,ORDDATE) ) if output will be greater than today date then it will be update with today date.

Example : ORDATE is '2021-09-26' so as per current condition ORDDATE will be 2021-09-29 but i want the result after calculating the date not more than today date. So the date will be ' 2021-09-27' If the ORDATE calculation below today date then it will be fine and not change in date required. Thanks for your reply

UPDATE : - As per suggestion i have add data with Script on sqlfiddle . If you review the data for visiting table for Oid 1 and 5 . Both updated data for OriginArrvDate is more than today date. I want to update the OriginArrvDate with today date when new ordDate is more than current date.

CodePudding user response：

I would use a CTE which makes it very easy to test your logic by applying a CASE expression (not a CASE statement) to the output of your existing CASE expression.

;WITH x AS
(
   SELECT Oid, ORDDATE, OriginArrvDate,
     CalcDate = CASE DATEPART(weekday, ORDDATE)
       WHEN 6 THEN DATEADD(DAY, 3, ORDDATE)
       WHEN 7 THEN DATEADD(DAY, 3, ORDDATE)
       WHEN 1 THEN DATEADD(DAY, 2, ORDDATE)
       WHEN 2 THEN DATEADD(DAY, 1, ORDDATE)
       ELSE ORDDATE END
   FROM dbo.visiting
   WHERE orddate >= CONVERT(DATE,DATEADD(DAY, -100, getdate()))  
   AND OriginArrvDate IS NULL
)
--SELECT *,
/* -- */ UPDATE x SET 
OriginArrvDate = CASE 
  WHEN CalcDate > GETDATE() THEN GETDATE() 
  ELSE CalcDate END
--FROM x;

Thanks for the fiddle, but I wrote a db<>fiddle here because I couldn't get SQLFiddle to properly show the update.

In addition to using DAY instead of dd I would also make sure to always specify a table's schema.