CAST() problem

[ArviLaanemets]

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?

[Minty]

If you use T-Sql

DATEDIFF(Day,DeliveryDate,Getdate()) as RemainingDays

Do you get the correct results?

[ArviLaanemets]

Have to check this in next week (I'm working on half-time from this year, and for this week my hours are done).

Usually I use UDF's created in a separate utilities DB on my SQL Server (there is one which works like DateSerial() in MS Office), when designing such queries, but for this case I wanted the script which can run on any other SQL Server. Your solution looks promising!

[ArviLaanemets]

DATEDIFF(Day,DeliveryDate,Getdate()) as RemainingDays
Do you get the correct results?

Yes, the results were correct! Thanks!

The weird thing is, that today the query with CAST() too didn't return negative values? Unfortunately I didn't save exactly same query which returned wrong day numbers, so no I don't know what did get wrong way in last week!

Meanwhile I had an idea I'd like to check out, but I don't want to risk it on our live SQL Server. I'll post a new thread about it now.