Using .net Datetime in sql query

Question

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)"

I'm working on an update to legacy code which is not customer facing, i.e. no time :( — Patrick
– Patrick, Commented Mar 31, 2010 at 12:11
It takes no more time to use a parameterized query than it does a concatenated one. — Chris Dunaway
– Chris Dunaway, Commented Mar 31, 2010 at 16:57

David M · Accepted Answer · 2010-03-31 11:59:48Z

7

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?

answered Mar 31, 2010 at 11:59

David M

73.2k13 gold badges164 silver badges187 bronze badges

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

1 Comment

Patrick Over a year ago

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

Marc Gravell · Accepted Answer · 2010-03-31 12:00:11Z

5

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.

answered Mar 31, 2010 at 12:00

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Comments

Chris Haas · Accepted Answer · 2010-03-31 12:55:28Z

1

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"))

answered Mar 31, 2010 at 12:55

Chris Haas

55.7k13 gold badges152 silver badges293 bronze badges

6 Comments

ParmesanCodice Over a year ago

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?

Patrick Over a year ago

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 Over a year ago

@ Parmesan, if you have a better way why not share it more fully in an answer?

ParmesanCodice Over a year ago

@Patrick, I'm not 100% sure, that's why I'm interested in Chris's reasoning behind his answer.

Chris Haas Over a year ago

@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.

|

Collectives™ on Stack Overflow

Using .net Datetime in sql query

3 Answers 3

1 Comment

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related