current date minus date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

vijayk - 09 Jan 2006 16:03 GMT

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

Reply to this Message

Knut Stolze - 09 Jan 2006 16:18 GMT

> 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

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Bohne - 10 Jan 2006 12:55 GMT

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

Reply to this Message

Brian Tkatch - 10 Jan 2006 15:40 GMT

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.

Reply to this Message

Brian Tkatch - 10 Jan 2006 15:53 GMT

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

Reply to this Message

Knut Stolze - 11 Jan 2006 10:49 GMT

> OK, wasn't thinking.
>
[quoted text clipped - 23 lines]
> DROP TABLE SESSION.A
> COMMIT

Even easier with date durations:

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

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Brian Tkatch - 11 Jan 2006 15:01 GMT

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

B.

Reply to this Message