Date conversion

[Jama]

I have dates stored as 20040904(yyyymm dd 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(DATET IME, STR(Date1), 112), 101),
CONVERT(CHAR(10 ),CONVERT(DATET IME, STR(Date2), 112), 101)
FROM Table1, Table2
WHERE DATEDIFF(DY, CONVERT(DATETIM E, STR(Date2), 112),
CONVERT(DATETIM E, 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

[Knut Stolze]

Jama wrote:

I have dates stored as 20040904(yyyymm dd 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(DATET IME, STR(Date1), 112), 101),
CONVERT(CHAR(10 ),CONVERT(DATET IME, STR(Date2), 112), 101)
FROM Table1, Table2
WHERE DATEDIFF(DY, CONVERT(DATETIM E, STR(Date2), 112),
CONVERT(DATETIM E, 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.

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(<da te>, 1, 4) || SUBSTR(<date>, 5, 2) || SUBSTR(<date>,
7, 2))

--
Knut Stolze
Information Integration
IBM Germany / University of Jena