Today I had a problem with a query, where I needed to calculate remaining days to given date.
In table, I have a date field (date, not datetime!), e.g. DeliveryDate. In query (run from stored procedure), the number of days remaining to DeliveryDate from today's date must be calculated. I.e. for start:
SELECT ..., DeliveryDate, CAST((DeliveryDate - GetDate()) AS smallint) AS RemaininigDays, ...
This works, but as GetDate() returns datetime, then all remaining days are shifted. -1 for today, 0 for tomorrow, etc.
I could calculate todays date as date value without problems
CAST(GetDate() AS Date)
- the time part was removed when this expression is returned as result field, but when using the same conversion in query expression
SELECT ..., DeliveryDate, CAST((DeliveryDate - CAST(GetDate() AS Date)) AS smallint) AS RemaininigDays, ...
I got an error. And when I instead created a variable @Today = CAST(GetDate() AS Date), and used this in query instead
SELECT ..., DeliveryDate, CAST((DeliveryDate - @Today) AS smallint) AS RemaininigDays, ...
, then I got the same error again!
The result was same, when instead CAST I tried CONVERT. Probably the cause is, that some remaining days will have value 0 (and <0 until converted to smallint, but why did it work in topmost query then?) .
Currently I simply added 1 to RemainingDays, but this doesn't feel as correct solution!
Any ideas?