How compare SQL Server Datetime from C#

Question

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.

I don't think there should be any issue in passing datetime from C# to sql server. What issue are you facing ? — shrekDeep
– shrekDeep, Commented Nov 19, 2014 at 14:23
If you store a datetime in SQL then it has no format. Same like a DateTime in C# - it also has no format. It is not a string. — TomTom
– TomTom, Commented Nov 19, 2014 at 14:26

marc_s · Accepted Answer · 2014-11-19 14:31:28Z

5

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

answered Nov 19, 2014 at 14:31

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BornToCode · Accepted Answer · 2016-08-09 14:19:10Z

2

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.

edited Aug 9, 2016 at 14:19

BornToCode

10.4k9 gold badges75 silver badges92 bronze badges

answered Nov 19, 2014 at 14:23

user927976

1 Comment

Martin.Martinsson Over a year ago

Uppercase "SS" should be lower case, dude!

Collectives™ on Stack Overflow

How compare SQL Server Datetime from C#

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related