Geek Answers Handbook

What is the best practice for querying dates in SQL when parameterized queries are not possible

My resume is to implement an interface that has a method that looks something like this: GetValuesSqlStatement below:

public string SqlPattern { get { ... } } //this varies; eg. "SELECT name FROM someTable WHERE startDate < {0}" public string DatabaseType { get { ... } } //this varies; eg. "SqlServer" public string GetValuesSqlStatement(List<object> paramValues) { //...desired logic here, using DatabaseType, SqlPattern and paramValues }

Now, since this should lead to the execution of the executable SQL statement, I cannot use the parameters when executing the query. And the interface I have to implement is not negotiable. What is the best way to make sure the dates in the results are interpreted correctly by the database query engine? Assuming paramValues ​​contains .NET DateTime objects, how should they be formatted into a string before connecting to a SQL template string? What is the most common universal date format in required databases? (for example, something like "dd-mmm-yyyy").

NB: I really need to worry about Sql Server from 2005 and Oracle 10g onwards. Thus, SQL must be valid T SQL and PL SQL and means the same thing in both cases.

+4
source share
3 answers

I think the only unambiguous date format for SQL Server is YYYYMMDD:

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx

http://www2.sqlblog.com/blogs/aaron_bertrand/archive/2011/09/20/bad-habits-to-kick-using-shorthand-with-date-time-operations.aspx

Oracle uses the DATE notation 'YYYY-MM-DD':

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements003.htm#BABGIGCJ

Although there may be a designation that works for both in some scenarios, I doubt that there is one that works for all possible regional server settings.

As you said, YYYY-MON-DD can be useful - this is Oracle by default.

+1
source

If you use the date format “yyyy-mm-dd” from any database, you should be fine. This complies with ISO 8601 ( http://www.iso.org/iso/date_and_time_format )

+1
source

I provide my own answer to this question, even if it deviates from the scope of the question, because it can be a useful suggestion for others with a similar question.

I just realized that I could just expect that every instance (or every client in another part of the world) can indicate a format string in the second part of the parameter holder. For instance. implementation:

 public string SqlPattern { get { return "SELECT name FROM someTable WHERE startDate < {0:yyyy-mm-dd}"; } }

And then my component does not need to worry about how to format the date at all. By default, I can use "yyyymmdd" or even better use a server culture to select the default value. Otherwise, use a user-defined template. This will be a general approach applicable to other types that also need to be formatted for a string for SQL.

0
source

More articles:


All Articles