I was researching a question made on the SQL Forums regarding the return results of the SQL 2008 Date/Time datatype. If you run the following code, the output is a full datetime output in the Query Results.
SELECT CAST(GETDATE() AS DATE)
The issue in question was already reported in a connect Feedback and identified as an error by Microsoft, however they posted it as fixed and under test 2/7/2008 so the issue still exists in the Feb CTP which is what brought up the question.
However, while searching for this I happened upon another Feedback that provides some good return information from Microsoft about the implicit conversions that occur during usage of the various date/time datatypes in SQL 2008. Like all other datatypes in SQL, the newer date/time types introduced in 2008 have a conversion hierarchy which is (highest to lowest):
- DATETIMEOFFSET
- DATETIME2
- DATETIME
- DATE or TIME
What this means is that when a DATE variable is compared to a DATETIME datatype, the DATE variable will be implicitly converted to a DATETIME. This is important to keep in mind when using these datatypes.
The other important thing that the feedback on this page provides is that the output of the DATEADD() function in TSQL has a DATETIME return datatype. This means that if you have a DATE variable @DateVar that you are doing a comparison with using
DATEADD(dd, 1, @DateVar)
against a DATE column in a table, you force an implicit conversion of the column in the table to the DATETIME datatype for the matchup.
No comments:
Post a Comment