How can I convert a timestamp to an ISO formatted date in db2?

Question

For example, if I have a field in the database that has a value of: 2010-10-20-12.00.00.000000

How can I get a numeric that contains '20101020'.

So I can do something like this:

SELECT * FROM file where date(timestamp) between 20101020 and 20101031

That doesn't seem to work though. Date() doesn't return it in ISO format.

Answer 1

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.