C# Finisar SQLite DateTime Comparison Problem

Question

My "task" database table look like this:

[title] [content] [start_date] [end_date]
[...] [...] [01.06.2010 20:10:36] [06.06.2010 20:10:36]
[...] [...] [05.06.2010 20:10:36] [06.06.2010 20:10:36]

And I want to find only those records that meet the condition that a given day is between start_date and end_date.

I've tried the following SQL expression:

SELECT * FROM task WHERE
strftime ('%d', start_date) <= @day
AND
@day <= strftime ('%d', end_date)

Where @day is an SQLiteParameter (eq 5). But no result is returned.

How can I solve this problem?

Thanks.

Answer 1

Your column names shouldn't be in single quotes as that will create strings. Instead you can use double quotes, or in this case you don't need quotes at all. Try this instead:

SELECT *
FROM task
WHERE strftime('%d', start_date) <= @day
AND @day <= strftime('%d', end_date)

or perhaps you mean this:

SELECT *
FROM task
WHERE date(start_date) <= @day
AND @day <= date(end_date)

Answer 2

Wait, misunderstood your intended query. This should work:

SELECT * FROM task WHERE
strftime ('%d', start_date) <= @day
AND
@day <= strftime ('%d', end_date)

Provided @day is actually a 2 digit text type. For it to work with numeric types, use:

SELECT * FROM task WHERE
CAST(strftime('%d', start_date) as integer) <= @day
AND
@day <= CAST(strftime('%d', end_date) as integer)

Answer 3

SELECT *
FROM task
WHERE @day BETWEEN date(start_date) and date(end_date)

Answer 4

Seems that functions working with data and time in SQLite3 does not accept like parameter a table column. Expects only Strings. But instead you can make comparisons of data using strings.

Example:

SELECT * FROM table WHERE
table_column> = "06.25.2010 00:00:00"
AND
table_column <"25.06.2010 23:59:59"

This work for me.