CODEWITHSUNDEEP
c#.netsqlvb.netdatetime
Patrick
Patrick

Reputation: 8300

Using .net Datetime in sql query

I have a DateTime object I want to compare against an sql datetime field in a where clause. I'm currently using:

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

But I believe datetimeVariable.ToString will return a different value depending on the culture where the system is running.

How would you handle this so it is culture independent?

EDIT : I won't be using paramatised sql in this code...

EDIT : following Parmesan's comment to one of the answers looks like the best method may be:

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

Upvotes: 5

Views: 5144

Answers (3)

Chris Haas
Chris Haas

Reputation: 55417

If you want ToString() to always come out regardless of culture then specify a specific culture:

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

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1062512

Parameters. Always parameters:

where @someVar <= DatetimeField

and add a parameter called "@someVar" with that value.

Solves (among other issues) problems with i18n / encoding, concatenation / injection and query-plan re-use.

Upvotes: 5

David M
David M

Reputation: 72840

Don't use string concatenation, use a parameterised query. Pass in a parameter value of type DateTime. This avoids the formatting issue altogether, improves performance for subsequent queries, and gets around the inherent vulnerabilities (SQL injection) that you lay yourself open to when forming SQL in this way.

"where @dateTime <= DateTimeField"

Then set the parameter @dateTime. If you need more, tell us a bit more about your code - straight ADO.NET, Enterprise Library, something else?

Upvotes: 7

Related Questions