CODEWITHSUNDEEP
sql-server
C.J.
C.J.

Reputation: 3527

Operand type clash: date is incompatible with int in dateadd

The following query works:

select dateadd(m, -5, getdate() - datepart(d, getdate()) + 1)

But when I tried to replace the getdate() with a specific date, example below:

select dateadd(m, -5, (convert(DATE,'2017-01-04') - (datepart(d, getdate()) + 1)))

I get the error saying Operand type clash: date is incompatible with int

What did I do wrong?

Upvotes: 1

Views: 8886

Answers (1)

M.Ali
M.Ali

Reputation: 69504

It is because GETDATE() returns DATETIME datatype ,You Can do -1 or +1 with Datetime values but not with Date values.

If you just changed your query a little bit , convert to datetime instead of Date it will work fine. 

select dateadd(   m
               , -5
               , (convert(DATETIME,'2017-01-04') - (datepart(d, getdate()) + 1))) 

                              ^-- Datetime instead of Date

Upvotes: 3

Related Questions