I always thought an ISO-formatted date was yyyy-mm-dd
. That's the one we get from DB2 with the expression:
char (date_column,iso)
But I don't think it's needed for your case, you should be able to do it with:
SELECT * FROM file where date(timestamp) between 20101020 and 20101031
into:
select *
from file
where timestamp >= '2010-10-20-00:00:00.000000'
and timestamp <= '2010-10-31-00:00:00.000000'
This is likely to run faster since you're not doing a calculation on each row in the database (a performance killer if the DBMS isn't smart enough to morph your query).
In other words, the only calculation is before the query runs, where those text fields are turned into timestamps.