CODEWITHSUNDEEP
c#sqlsql-serverdatetime
Carol
Carol

Reputation: 563

How compare SQL Server Datetime from C#

I've got to make a query from C# and compare with a datetime. Datetime is stored in my database like this: 

2014-11-09 00:00:01

and I'm using this query:

SELECT * 
FROM Table 
WHERE DATETIMEVAR = '19/11/2014 0:00:01' AND OTHERVAR = 1

But it's not in the same format. Is there a way to convert from System.DateTime in C# to dates in SQL Server, or a way to cast from SQL Server the datetime in that format?? Should I check with like instead of = in Where clause.

Thanks.

Upvotes: 1

Views: 6635

Answers (2)

user927976
user927976

Reputation:

If you are generating the query from a DateTime object, just do date.ToString("yyyy-MM-dd HH:mm:ss") to convert it to that format in your query.

Upvotes: 2

marc_s
marc_s

Reputation: 754488

The best way would be to have a parametrized query in C#, and then set your date time parameter as a datetime (instead of relying on converting dates back and forth to and from strings).

So you should have something like:

SELECT * 
FROM Table 
WHERE DATETIMEVAR = @DateTimeValue AND OTHERVAR = 1

and then define that @DateTimeValue parameter as datetime and set it as datetime

That approach:

  • prevents any SQL injection vulnerability
  • avoid any string/formatting issues - it compares a DATETIME to a System.DateTime in their native format

Upvotes: 5

Related Questions