CODEWITHSUNDEEP
c#sqldatetime
Guilherme Campos
Guilherme Campos

Reputation: 379

How to make a database consult using a date and time variable?

I have the following line code used to retrieve data from database:

DataTable newEquipmentTable = database.Connection.RetrieveData(database.AdapterType, string.Format("SELECT ID FROM Equipment WHERE Name = '{0}' AND CreatedOn = '{1}'", equipment.Name, equipment.CreatedOn.ToString("yyyy-MM-dd HH:mm:ss.fff")));

It returns an error saying "The conversion of varchar data type to a datetime datatype resulted in an out-of-range value"

When I make a direct consult, using the following query it returns the expected result.

SELECT ID FROM Equipment WHERE Name = 'aa' AND CreatedOn = '2012-04-17 19:42:49.650'

What am I doing wrong?

Upvotes: 0

Views: 162

Answers (3)

Malhar Chudasama
Malhar Chudasama

Reputation: 353

What is the value of equipment.CreatedOn?

I think problem with the culture setting either in sql-server or your application. It cannot directly covert your date. User culture setting will solve problem.

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460058

You're passing a String value to select a datetime field. So you need to convert it to a datetime first:

Convert(datetime,'2012-04-17 19:42:49.650', 102)

Apart from that you might be open for SQL-Injection as @Mr47 has mentioned, use SqlParameters.

Upvotes: 2

Cristian Lupascu
Cristian Lupascu

Reputation: 40516

Please note that SQL's datetime type cannot hold values prior to 1753-01-01.

Just inspect the SQL string with the debugger before it is used to query the database in order to find the problem.

However, the best thing to do is to use a parameterized query (like in this example); otherwise you're vulnerable to SQL Injection.

Upvotes: 0

Related Questions