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.
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.
精彩评论