SQL Queries – DateTime should be passed in single quotes


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)

 

Other Reference:

* The bug I found today 🙂

* http://stackoverflow.com/questions/6119369/simple-datetime-sql-query

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s