CODEWITHSUNDEEP
sqlsql-server
Bianca
Bianca

Reputation: 9

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

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.

Upvotes: 0

Views: 307

Answers (2)

Paul Alan Taylor
Paul Alan Taylor

Reputation: 10680

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.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522506

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;

Upvotes: 0

Related Questions