Operand type clash: datetime2 is incompatible with int (Between)

Question

I am trying to get records inbetween two dates, however when I run the SQL I get the following error: Operand type clash: datetime2 is incompatible with int ``` SELECT M.Id, M.MTimeInt, M.Date, CAST(D.Name AS TEXT) as Name FROM C JOIN N ON C.N_Id=N.Id JOIN M ON M.N_Id=N.Id JOIN MDish ON MDish.M_Id=M.Id JOIN D ON D.Id=MDish.D_Id WHERE C.Id=110 AND M.Date BETWEEN 2012-05-28 AND 2012-06-08 ``` The SQL looks correct, but I can't seem to figure out why it is complaining, any idea? Thanks

Accepted Answer

Try:

```
BETWEEN '2012-05-28' AND '2012-06-08'
```

Otherwise it's `2012 minus 05 minus 28`, which equals `1979` and is an integer and not a date.

Operand type clash: datetime2 is incompatible with int (Between)

Solution

Problem