CODEWITHSUNDEEP
sqlsql-servervb.net
400_the_cat
400_the_cat

Reputation: 881

SQL: how to check for a specific DateTime

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

Upvotes: 1

Views: 5293

Answers (4)

AllenG
AllenG

Reputation: 8190

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.

Upvotes: 1

spinon
spinon

Reputation: 10857

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

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332791

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.

Upvotes: 2

Jon Skeet
Jon Skeet

Reputation: 1504172

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.

Upvotes: 5

Related Questions