I need to convert below code from sql to oracle SQL

Question

I need to convert below code from SQL Server to Oracle:

Update A
         Set 
          A.ShiftName=@ShiftName,
          A.ShiftStartTime=DATEADD(day,DATEDIFF(day,0,AttendanceDate),
          CAST(S.ShiftStartTime as varchar(8))),
          A.ShiftEndTime=DATEADD(day,DATEDIFF(day,case when @ShiftName='NIGHT' then -1 else 0 end ,
          AttendanceDate),CAST(S.ShiftEndTime as varchar(8))),
          A.ModifiedBy=@UpdatedBy, 
          A.ModifiedOn=GETDATE()
          from Attendance A left join ShiftMaster S on S.[Shift]=@ShiftName
          where AttendanceId=@AttendanceId
          

Answer 1

In Oracle:

• DATEADD(day, ...) should be implemented using the + operator
• DATEDIFF should be implemented using the - operator (but it is unclear what you are trying to achieve by finding the difference between 0 and a date).
• [identifier] is not valid syntax and you should use "identifier" if the identifier is case-sensitive or identifier if it is not case-sensitive.
• @named_bind_variable should be :named_bind_variable.
• GETDATE() should be SYSDATE or TRUNC(SYSDATE) (if you want midnight of the current day).
• It is not clear why you would want to use CAST to convert a value to a string and try to add a string to a date as you have not told us what data the columns are holding but that looks wrong.

That should be enough for you to fix your own code.

You do NOT want to convert your code to PL/SQL as PL/SQL is Oracle's procedural language. You want to convert it to Oracle's SQL dialect (and asking to convert it from SQL to Oracle SQL is meaningless what you probably mean is from SQL Server SQL to Oracle SQL).