System function to return just the date and no time
You will notice a DATE related theme of the posts today.
This one is about the lack of a system function that returns JUST the date and
DOESNT include any tiem component.
Due to time constraints they didn't include such a function, seems like a car
without a steerwheel to me, but time is time and thats where we are.
You can vote on Erlands suggestion for such a function and also read some
comments from MS as to why they didn't include it
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=293333
The biggest gotcha you will find is if you use the new date data type and
need to query everything for today.
declare @tab
table (col1
date)
insert @tab
values (getdate())
select
*
from @tab where col1
= sysdatetime ()
This won't return anything because rather than reducing the datetype of
sysdatetime down to the DATE type, data type precendence states that
the DATE column will be elevated to datetime2 (the data type returned by
sysdatetime). This seems really odd for me for dates. I would have expected DATE
and TIME to have been above the combined data types because the limit
of domain of this types is the same but the granularity is different, unlike
ints and big ints which have different limits.
So to get round this you have to cast your function call to the DATE type
i.e
select * from @tab where col1 = cast(sysdatetime()
as date)
This feels wrong. Interestingly if you compare against any other data type
you will get an implicit cast to the DATE type because of the precendence.
declare @dv
varchar(100) = sysdatetime ()
select
* from @tab
where col1 =
@dv
-