Search code examples
.netsqldate-formattingquerying

What is the best practice to query dates in SQL when parameterised queries are not possible


My brief is to implement a interface which has a method that looks something like '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, because this must produce an executable SQL statement, I can't use parameters in the execution of the query. And the interface I must implement is non-negotiable. What is the best way to proceed to make sure the dates in the result are interpreted by the database query engine correctly? Assuming that the paramValues contain .NET DateTime objects, how should these be formatted to string before plugging into the SQL pattern string? What is the most common universal date format across must databases? (eg. something like 'dd-mmm-yyyy').

NB: I only really need to worry about Sql Server from 2005 onwards and Oracle 10g onwards. So the SQL must be valid T SQL and PL SQL and mean the same thing in both flavours.


Solution

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

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

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

    While there may be a notation which works for both in some scenarios, I doubt there is one which works for both with all possible regional server settings.

    Like you said, YYYY-MON-DD might be useful - it's Oracle's default.