current date minus date

[vijayk]

Hi all,

I have a field which has data as YYYYMMDD, and I have to find the age
of the person by substracting it from current date. can you please
please advice...

thanks

[Knut Stolze]

vijayk wrote:

Hi all,

I have a field which has data as YYYYMMDD, and I have to find the age
of the person by substracting it from current date. can you please
please advice...

VALUES current date - date('08-01-1975')

The result is a date duration and you can find a description for those here:
http://tinyurl.com/7r7ll

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Bohne]

I don't think you can do that.
Not with the data in the form YYMMDD.

At least I could not make it happen.

It works if your field has data as 'YYYY-MM-DD'.
Maybe you can change that.

db2 "select * from sim.date2"
MYDATE
----------
1971-08-19
db2 "select days(current date)- days(mydate) age_in_days from sim.date2"

AGE_IN_DAYS
-----------
12563

[Brian Tkatch]

DECLARE GLOBAL TEMPORARY TABLE A (BirthDay DATE)

INSERT INTO SESSION.A VALUES('01-07-1975')
INSERT INTO SESSION.A VALUES('01-08-1975')
INSERT INTO SESSION.A VALUES('01-09-1975')
INSERT INTO SESSION.A VALUES('01-10-1975')
INSERT INTO SESSION.A VALUES('01-11-1975')
INSERT INTO SESSION.A VALUES('01-12-1975')
INSERT INTO SESSION.A VALUES('01-13-1975')
INSERT INTO SESSION.A VALUES('01-14-1975')
INSERT INTO SESSION.A VALUES('01-15-1975')
SELECT
Birthday,
YEAR(CURRENT DATE) - YEAR(BirthDay)
+ CASE
WHEN MONTH(BirthDay) > MONTH(CURRENT DATE)
OR (MONTH(BirthDay ) = MONTH(CURRENT DATE)
AND DAY(BirthDay) >= DAY(CURRENT DATE)) THEN 1
ELSE 0
END CASE
FROM SESSION.A

DROP TABLE SESSION.A
COMMIT

HTH,
B.

[Brian Tkatch]

OK, wasn't thinking.

DECLARE GLOBAL TEMPORARY TABLE A (BirthDay DATE)

INSERT INTO SESSION.A VALUES('01-07-1975')
INSERT INTO SESSION.A VALUES('01-08-1975')
INSERT INTO SESSION.A VALUES('01-09-1975')
INSERT INTO SESSION.A VALUES('01-10-1975')
INSERT INTO SESSION.A VALUES('01-11-1975')
INSERT INTO SESSION.A VALUES('01-12-1975')
INSERT INTO SESSION.A VALUES('01-13-1975')
INSERT INTO SESSION.A VALUES('01-14-1975')
INSERT INTO SESSION.A VALUES('01-15-1975')
SELECT
Birthday,
YEAR(CURRENT DATE) - YEAR(BirthDay)
- CASE
WHEN MONTH(BirthDay) < MONTH(CURRENT DATE)
OR (MONTH(BirthDay ) = MONTH(CURRENT DATE)
AND DAY(BirthDay) < DAY(CURRENT DATE)) THEN 1
ELSE 0
END CASE
FROM SESSION.A

DROP TABLE SESSION.A
COMMIT

[Knut Stolze]

Brian Tkatch wrote:

OK, wasn't thinking.

DECLARE GLOBAL TEMPORARY TABLE A (BirthDay DATE)

INSERT INTO SESSION.A VALUES('01-07-1975')
INSERT INTO SESSION.A VALUES('01-08-1975')
INSERT INTO SESSION.A VALUES('01-09-1975')
INSERT INTO SESSION.A VALUES('01-10-1975')
INSERT INTO SESSION.A VALUES('01-11-1975')
INSERT INTO SESSION.A VALUES('01-12-1975')
INSERT INTO SESSION.A VALUES('01-13-1975')
INSERT INTO SESSION.A VALUES('01-14-1975')
INSERT INTO SESSION.A VALUES('01-15-1975')
SELECT
Birthday,
YEAR(CURRENT DATE) - YEAR(BirthDay)
- CASE
WHEN MONTH(BirthDay) < MONTH(CURRENT DATE)
OR (MONTH(BirthDay ) = MONTH(CURRENT DATE)
AND DAY(BirthDay) < DAY(CURRENT DATE)) THEN 1
ELSE 0
END CASE
FROM SESSION.A

DROP TABLE SESSION.A
COMMIT

Even easier with date durations:

VALUES INT((CURRENT DATE - DATE('1975-01-08')) / 10000)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Brian Tkatch]

Yes, it does. I was thinking of day of year, and how it would fail on
2/29. DATE() seems to work though.

B.