CODEWITHSUNDEEP
sqlsql-serverdatetime-format
Ludovic Aubert
Ludovic Aubert

Reputation: 10528

eomonth operand type clash (Msg 2016)

DECLARE @DateMin AS date = DATEFROMPARTS(2019, 7, 19);
DECLARE @DateTimeMax AS datetime = EOMONTH(@DateMin)+1;

Error Messages:

Msg 2016, Level 16.
operand type clash. date is incompatible with int.

Error happens on the second line. Using SQL Server 2016.

Upvotes: 1

Views: 144

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

In SQL Server, you can add a number to a datetime to add days, but not to a date. So, you could fix this in two ways if you insist on this construct:

DECLARE @DateMin datetime = DATEFROMPARTS(2019, 7, 19);
-----------------^ change type to datetime
DECLARE @DateTimeMax datetime = DATEADD(DAY, 1, EOMONTH(@DateMin));

Or:

DECLARE @DateMin date = DATEFROMPARTS(2019, 7, 19);
DECLARE @DateTimeMax datetime = CONVERT(datetime, DATEADD(DAY, 1, EOMONTH(@DateMin)));
--------------------------------^ add explicit conversion

I do think that dateadd(day, ) is a better solution because the intent is clearer.

Upvotes: 1

Thom A
Thom A

Reputation: 95564

The error is telling you the problem, date and int are incompatible. Use DATEADD:

DECLARE @DateMin AS date = DATEFROMPARTS(2019, 7, 19);
DECLARE @DateTimeMax AS datetime = DATEADD(DAY, 1, EOMONTH(@DateMin));

Upvotes: 4

Related Questions