SQL: how to check for a specific DateTime

Question

i need to check for a specific DateTime value in my table from my code (VB.NET) and i don't know how to format the DateTime as a string. i've read that SQL Server will recognize my string if it's in either date, time, or date and time format. i mean:

'May 15, 2004'
'5/15/2004'
'20040515'
'2004 4 am'

will SQL Server recognize these strings as valid DateTime values? i'm curious because if i check the actual DateTime values in the table they are in this format:

2/2/2006 3:49:33 PM

Answer 1

Don't put the date/time value in the SQL query in the first place - use a parameterized query and then you don't need to know or care what format SQL Server would parse literals as. You put the placeholder in the SQL, and specify the value as a DateTime in the parameter collection.

You should be using parameterized SQL as a matter of course, in fact - not only does it get rid of formatting and parsing problems like this, but possibly more importantly it's the single most effective weapon against SQL injection attacks.

Answer 2

If not using a parameterized query, use CAST/CONVERT to explicitly change a string to a DATETIME:

SELECT CAST('2/2/2006 3:49:33 PM' AS DATETIME)

On my SQL Server 2005, that returns to me:

2006-02-02 15:49:33.000

Mind that the default date format in SQL Server can be different than what you provide.

Answer 3

This has always been safe that I have found: YYYY-MM-DD HH:MI:SS

Answer 4

If you're comparing DateTime to DateTime, you don't have to worry about conversion, necessarilly, but yes, Sql Server (at least as of 2k8, and I believe 2k5 as well) will automatically parse a DateTime from a string. That is, if you pass '5/15/2004' it will see 5/15/2004 12:00:00 AM or something similar.

a better way, though, is to use SqlParameters in your SqlCommand from Code.