Date conversion

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jama - 27 Jan 2005 15:35 GMT

I have dates stored as 20040904(yyyymmdd numeric) in DB2 database. I
want to get the difference between two dates. I also have the same
database in SQL Server database and I am using the following SQL to do
the arithmetic in the where clause.

SELECT CONVERT(CHAR(10),CONVERT(DATETIME, STR(Date1), 112), 101),
CONVERT(CHAR(10),CONVERT(DATETIME, STR(Date2), 112), 101)
FROM Table1, Table2
WHERE DATEDIFF(DY, CONVERT(DATETIME, STR(Date2), 112),
CONVERT(DATETIME, STR(Date1), 112)) > 16

I want an equivalent DB2 syntax to allow me to calculate the difference
between two dates stored as numbers in yyyymmdd numeric format.

Thanks

Reply to this Message

Knut Stolze - 31 Jan 2005 07:37 GMT

> I have dates stored as 20040904(yyyymmdd numeric) in DB2 database. I
> want to get the difference between two dates. I also have the same
[quoted text clipped - 9 lines]
> I want an equivalent DB2 syntax to allow me to calculate the difference
> between two dates stored as numbers in yyyymmdd numeric format.

The DATE function supports several different input formats.

One takes an integer (as number of days since 0001-01-01) directly. So you
could do some calculations to convert your number into the number of days.
This is not correct because it assumes 30 days per month and ignores leap
years, but you should get the idea:

VALUES DATE((<date> / 10000) * 365 + MOD(<date>, 10000) * 30 + MOD(<date>,
100))

Another version takes a string. There you have to parse your string and
rebuild it in a format accepted by DB2.

VALUES ( DATE(SUBSTR(<date>, 1, 4) || SUBSTR(<date>, 5, 2) || SUBSTR(<date>,
7, 2))

Signature

Knut Stolze
Information Integration
IBM Germany / University of Jena

Reply to this Message