473,326 Members | 2,023 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

current date minus date

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

Jan 9 '06 #1
6 26274
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
Jan 9 '06 #2
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

Jan 10 '06 #3
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.

Jan 10 '06 #4
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

Jan 10 '06 #5
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
Jan 11 '06 #6
Yes, it does. I was thinking of day of year, and how it would fail on
2/29. DATE() seems to work though.

B.

Jan 11 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Piotr Pietrowski | last post by:
Hello everybody, I have a *big* problem. I thought its not that big problem for you professionals... Anyway, I have a begin date which has 3 dropdown boxes (day/Month/Year). The same for the...
1
by: Arjen | last post by:
Hi, How can I calculate the date 2 years ago? I get a problem with datetime minus int (year). Can someone help? Thanks!
6
by: sreekandan | last post by:
Hi Now im doing one project to maint the login and logout time of the organization. So I need the VB codings to get the Current time and Date with detail. So kindly reply me anyone.
4
stormrider
by: stormrider | last post by:
Hi all, I'm trying to implement a Perl structure. My small program will do the followings; Take the birth date of the user as input. (Month's will be input as strings.) Take the current time...
2
by: Drum2001 | last post by:
Hello, I am having isues with the following: I have two forms, a MAIN FORM with a SUB FORM: Within the MAIN FORM, I have an unbound textbox (Date Format) and a command button. Onload, the...
6
by: Gilberto | last post by:
Hello I have a form where the user enters some pricing information. I need to store in the pricing table (fields time and date) the time and date WHEN the information was entered, so that later...
2
by: DThreadgill | last post by:
Not sure how to begin with this one. My table consists of: Branch# (number, double) EntryDate (datetime, mm/dd/yyyy hh:mm:ss am/pm) One branch can have many entry dates (i.e, Branch # 76...
1
by: ShwetaJain | last post by:
Hi... I am able to get the type of the message as IPM.Appointmnet in the inbox but i am unable to get the due date, start date, for that appointment.... I am using MAPI in exchange server but i...
1
by: smdmca | last post by:
I have Julian date, I want to convert it into date. Is there any function in MySql to convert Julian date to date eg- Julian Date- 2455116 Date - Oct - 12, 2009
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.