The data types date and datetime are incompatible in the subtract operator

Hi Guys,

getting error for datetime, here is my query:
DATEADD(m, INCR_TIME * 6, END_DT) - GETDATE() it does not like the GETDATE() subtract? what is the best way to do this?

END_DT =date
INCR_time =SmallInt--- like 1, 2 4

Thanks!

your expression subtracts a date from an integer. What does it mean to subtract 42 from April 24,2018?

Correct, whats the better of doing this?

I can't tell. What would be the answer to 42 - April 24? What are you expecting?

1 Like

Isn't 42 the answer to everything?

1 Like

What is the expected result - are you looking for a date or the difference in days/months/weeks?

Yes basically how many day left from step date.

INCR_TIME *6 in month adds to END_DT which becomes STEPDATE and then, How many days left from the SETPDATE ( which is STEPDate - GETDATE() ) which is 32.337 days. hope I am clear.

image

try using DATEDIFF

To confirm - you are attempting to get the column DAYSLEFT which is the difference between today and 6 months from the END_DT.

DATEDIFF(minute, GETDATE(), DATEADD(month, INCR_TIME * 6, END_DT)) / 1440.0

Select datediff(minute, getdate(), dateadd(month, 6, '2017-11-17')) / 1440.0 -- 21.358333

Thanks Jeff, yes correct , I came up wit this as well as Gbritton suggested:

(DATEDIFF (day, Getdate(), DATEADD(m, INCR_TIME * 6, END_DT)) which does the same as your with less decimal, float points..

Only thing is now I am getting MINUS for days left? any idea why?
image

Think I know why because of DIFF between 2 dates, 2017-05-28 and get date...

6 months from the end date defines your step date. The step date is less than today so your days left will be less than 0.

1 Like