CODEWITHSUNDEEP
db2
wkm
wkm

Reputation: 1762

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

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.

Upvotes: 3

Views: 12760

Answers (1)

paxdiablo
paxdiablo

Reputation: 881293

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.

Upvotes: 5

Related Questions