FirebirdSQL Timestamp comparison

I have a database table that includes a Timestamp column.
How can I select from that table while comparing with the timestamp column?

The following SQL returns rows where TimestampColumn is outside the selected dates, i.e. ‘2015-03-21’ as well as ‘2015-06-23’

FROM Table
WHERE TimestampColumn BETWEEN CAST('2015-05-01' AS timestamp) AND CAST('2015-05-31' AS timestamp)

I tried using the alternative date comparison, but that didn’t work either (returned the same as above):

FROM Table
WHERE TimestampColumn >= CAST('2015-05-01' AS timestamp) AND TimestampColumn <= CAST('2015-05-31' AS timestamp)

In addition I have also tried using CAST('2015-05-01' AS date) without it helping at all.

To further complicate/simplify things (depends on your perspective) I need to do this in C#.Net using the Firebird database providers. I have the same SQL there, but I use parameters instead. This yields exactly the same result as the first SQL example:

using (FbConnection con = new FbConnection(myConnectionString)) {
    FbDataAdapter adapter = new FbDataAdapter();
    adapter.SelectCommand = new FbCommand(
        @"SELECT *
        FROM Table
        WHERE TimestampColumn BETWEEN @From AND @To", con);
    adapter.SelectCommand.Parameters.Add("@From", FbDbType.TimeStamp);
    adapter.SelectCommand.Parameters.Add("@To", FbDbType.TimeStamp);
    adapter.SelectCommand.Parameters["@From"].Value = myFromValue; // myFromValue is DateTime
    adapter.SelectCommand.Parameters["@To"].Value = myToValue; // myToValue is DateTime
    DataTable dt = new DataTable();

What am I missing here?

Source: .net

Leave a Reply