Please help. I get this SQL Error: Msg 206, Level 16-CodePudding

I get this error message:

Msg 206, Level 16, State 2, Line 9
Operand type clash: datetime2 is incompatible with int

with this part of my query:

select * from Attendance where workdate = (Select Max(Workdate)-1 from Attendance)
order by WorkDate desc

Note: I am new to sql and still learning.

CodePudding user response：

If you want to subtract one day from the max work date in your table, then use the DATEADD() function:

SELECT *
FROM Attendance
WHERE workdate = (SELECT DATEADD(day, -1, MAX(Workdate)) FROM Attendance)
ORDER BY WorkDate DESC;

CodePudding user response：

Use DATEADD to add or subtract from dates.

select 
    * 
from 
    Attendance 
where 
   workdate = 
   (Select DATEADD(day,-1,Max(Workdate)) from Attendance)
order by WorkDate desc

Also, if you have the ability to store the maximum working day in a variable beforehand, prefer that over the sub-select on every row.