I learnt a new thing today.. I learnt that a DateTime value should always be passed within single quotes in a query.
Found this out when we were debugging a Query, used in our code, to find the cause of a bug I raised. The issue caused was due to DateTime value being passed in the SELECT query without being enclosed in single quotes.
On further research, I found that DateTime should always be enclosed within single quotes in all queries – whether it is a SELECT query, an UPDATE query, or an INSERT query. Otherwise, the DateTime value is not read/written correctly by the SQL Server.
For example, for the below given query,
SELECT * FROM TABLENAME WHERE DateTime >= 12/04/2011
No error will be raised by the SQL server, but it will divide the date value given (12/04/2011) and get zero, and then take the minimum possible DateTime value as the (default date + 0)
Making the effective SQL query into:
SELECT * FROM TABLENAME WHERE DateTime >= '1900-01-01 00:00:00.000'
Hence, though no error/exception is raised, and the query does appear to work, the results that you get from the query are incorrect.
A similar problem occurs when you pass DateTime values without quotes in INSERT and UPDATE queries.
(Microsoft Support: a value without quotes that is used as a datetime value is treated as the default date plus the number of days equal to the computed amount of the assigned value)
* The bug I found today 🙂