views:

315

answers:

3

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)"
+7  A: 

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?

David M
Should have mentioned in the question, I'm working on an update to legacy code which is not customer facing and will not be converting the whole app (or even this part of it) to use paramatised sql
Patrick
+5  A: 

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.

Marc Gravell
+1  A: 

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"))
Chris Haas
Why would you recommend this over the ISO date format. i.e. DateTime.Now.ToString("s"), this **should** always work with SQL as the SQL box could have a different culture?
ParmesanCodice
Thanks Chris. (however) If you're reading this answer and thinking, "that's how to do it," read the other answers for a better method that doesn't happen to fit my requirements.
Patrick
@ Parmesan, if you have a better way why not share it more fully in an answer?
Patrick
@Patrick, I'm not 100% sure, that's why I'm interested in Chris's reasoning behind his answer.
ParmesanCodice
Patrick
@ParmesanCodice, ISO date format works great, too, and is in fact shorter (and therefore probably better), I was just showing how to specify a specific culture.
Chris Haas