All geek questions in one place

Using .net Datetime in sql query

I have a DateTime object that I want to compare with the sql datetime field in the where clause. I am currently using:

"where (convert( dateTime, '" & datetimeVariable.ToString & "',103) <= DatetimeField)"

But I believe datetimeVariable.ToString will return a different value depending on the culture in which the system works.

How would you handle this, so that is cultural independence?

EDIT: I will not use paramatised sql in this code ...

EDIT: Parmesan's next comment on one of the answers looks like the best way:

 "where (convert( dateTime, '" & datetimeVariable.ToString( "s" ) & "',126) <= DatetimeField)"
+4
source share
3 answers

If you want ToString () to always exit regardless of culture, specify the specific culture:

  Dim D = DateTime.Now.ToString(System.Globalization.CultureInfo.InvariantCulture) '-or- Dim D = DateTime.Now.ToString(New System.Globalization.CultureInfo("en-us"))
+1
source

Do not use string concatenation; use a parameterized query. Go to the value of the parameter of type DateTime . This avoids the formatting problem in general, improves performance for subsequent queries, and bypasses the inherent vulnerabilities (SQL injections) that you open to generate SQL in this way.

 "where @dateTime <= DateTimeField"

Then set the @dateTime parameter. If you need more, tell us a little more about your code - directly ADO.NET, the corporate library, something else?

+7
source

Parameters. Always options:

 where @someVar <= DatetimeField

and add a parameter named "@someVar" with this value.

Solves (among other things) problems with i18n / encoding, concatenation / injection and reuse of the query plan.

+5
source

Source: https://habr.com/ru/post/1305659/

More articles:


All Articles