Home      Mainframe Forum      Mainfarme Tutorials      IBM Manuals      Mainframe Interview Questions      Mainframe Books      IT News     SiteMap     Downloads

 MAINFRAME - TIP OF THE DAY : programming pearls - Good judgement comes from experience, and experience comes from bad judgement.

 Web mainframegurukul.com

Programmers Voted for below topics.      Please Vote for good Posts. Votes Salutes
Author Message

Member

Joined: 08 Sep 2012
Posts: 5

 0 salutes

 Posted: Mon Sep 10, 2012 4:53 pm    Post subject: how to calculate age Hi, I have a DB2 table where in birth date is stored as birthdate char-08 in 'YYYYMMDD' format. Now I have to calculate if he has completed 18 years. How can I achive this? Many thanks for yyour reply. edited by dbz to remove 'cool'
 <-- Click on right mark icon. If this post answer your question.

Active Member

Joined: 24 May 2012
Posts: 714

 0 salutes

 Posted: Mon Sep 10, 2012 5:37 pm    Post subject: Subtract 18 from YYYY. If new complete value is less than or equal to Brithdate, then the person is 18. It works because the date fields are in that order. Mess them around and it won't work in one shot.
 <-- Click on right mark icon. If this post answer your question.

Member

Joined: 08 Sep 2012
Posts: 5

 1 salutes

Posted: Mon Sep 10, 2012 6:34 pm    Post subject:

 William Collins wrote: Subtract 18 from YYYY. If new complete value is less than or equal to Brithdate, then the person is 18. It works because the date fields are in that order. Mess them around and it won't work in one shot.

Hi William ,
Subtracting the year works in most cases but it does not works in below case where

Current date = 20120910
Birth date = 19941001

diff in year = +18
diff in month = -1
diff in days = +9
 <-- Click on right mark icon. If this post answer your question.

Active Member

Joined: 24 May 2012
Posts: 714

 1 salutes

 Posted: Mon Sep 10, 2012 8:03 pm    Post subject: Well, I've clearly written it the wrong way around. Otherwise someone born yesterday would be 18. If Birthdate is less than or equal to calculated date (YYYY-18)MMDD then the person is 18 or older.
 <-- Click on right mark icon. If this post answer your question.

Moderator

Joined: 02 Oct 2006
Posts: 975

 0 salutes

Posted: Mon Sep 10, 2012 8:23 pm    Post subject:

William,

one of the problems is that char ( 8 ) ends up looking like char(cool

i corrected his initial post,
but since the TS has a date stored in a char field,
the sql is a little more complex.
the birthdate column must be cast to a date type first, before the subtraction.

so the answer for our sql challenged poster would be something along the lines of

 Code: SELECT person FROM table WHERE (birthdate in char format cast as a date) < current_date - 18 years

or

 Code: WHERE birthdate < ( (current_date - 18 years) cast as char(8) )

or

 Code: WHERE (birthdate in char format cast as a date) + 18 years < current_date

to cast birthdate as date, you need to
date(substr(birthdate,1,4) || char('-',1) || substr(birthdate,5,2) || char('-',1) || substr(birthdate,7,2))

conversely
substr(char(current_date - 18 years,ISO),1,4) || substr(char(current_date,ISO),6,2) || substr(char(current_date,ISO),9,2)

if you are not using db2 v9, you need to check your sql reference for
the proper CHAR cast function parameters.
_________________
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
 <-- Click on right mark icon. If this post answer your question.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT + 5 Hours Page 1 of 1

 Jump to: Select a forum This Website----------------Admin AnnouncementsModerators Only APPLICATION PROGRAMMING----------------COBOLJCLDFSORT , ICETOOL & UtilitiesCICSEASYTRIEVE DATA MANAGEMENT SYSTEMS----------------DB2 SQL - DB2 PROGRAMMINGVSAM - File systemIMS DBIDMS TOOLS----------------XPEDITERFILE-AIDCHANGE MANENDEVOR, COOLGEN , PANVALET and CA-7 OTHER LANGUAGES----------------TSO, CLIST & REXXPL/IASSEMBLERIMS/DC CAREERS / JOBS DISCUSSIONS / INTERVIEW QUESTIONS----------------JOB VACANCIESJOB DISCUSSIONABEND CODES / ERROR CODESDB2 INTERVIEW QUESTIONSCOBOL INTERVIEW QUESTIONSJCL INTERVIEW QUESTIONSVSAM INTERVIEW QUESTIONSCICS INTERVIEW QUESTIONSINTERVIEW QUESTIONS OTHER MAINFRAME SUBJECTS----------------XMLMQSERIES & WEBSPHEREOTHER SUBJECTS GENERAL FORUMS----------------EntertainmentGeneral DiscussionPersonality DevelopmentOff TopicsSuggestions & Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

  Topics   Replies   Author   Views   Last Post Related topics jcl experts-3 years experience 6 krish 10590 Wed Feb 03, 2016 11:09 pmacademyindia4 Mainframe Professionals required - 2 - 7 years experience 0 muraligaru1 4552 Fri Dec 18, 2009 9:51 ammuraligaru1 Testing Jobs - 1+ Years Exp. - IBM INDIA 0 Vamsi99 2640 Sun Jun 21, 2009 10:57 amVamsi99 Requirement in INAUTIX - CHENNAI Exp - 3 to 6 Years 0 Vamsi99 2609 Wed Jan 07, 2009 7:27 pmVamsi99 Vision Plus Developers with 1+ years exp for Capgemini 0 Vamsi99 2331 Thu Nov 27, 2008 3:14 pmVamsi99

 Cobol TutorialThis cobol tutorial covers most of the important topics like STRING, UNSTRING, COMP, COMP-3..... DB2 TutorialDB2 Tutorial focuses on DB2 COBOL Programming. Explains in simple language. Some Chapters are locked, Forum members have free access to these chapters CICS TutorialThis CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming... JCL TutorialThis is most popular JCL tutorial from mainframegurukul. It does contain important jcl .... SORT TutorialThis Tutorial covers all important aspects of DFSORT. Has more SORT examples INTERVIEW QuestionsThis page covers important interview questions Mainframe Jobs Mainframe Jobs posted by members of mainframegurukul forum FaceBook PageMainframeGurukul FaceBook Page LinkedIn PageMainframeGurkul Linkedin Page

 Online ABEND Reference ||  JCL References ||  COBOL References ||  VSAM References ||  Tutorials by Drona Series ||  SQL tutorial ||  BOOKS  ||  DB2 INTERVIEW QUESTIONS ||  COBOL INTERVIEW QUESTIONS  ||  JCL INTERVIEW QUESTIONS ||  JCL2 INTERVIEW QUESTIONS ||  VSAM INTERVIEW QUESTIONS ||  CICS INTERVIEW QUESTIONS  ||  Online tutorials ||  Online ABEND Reference ||  JCL References ||  COBOL References ||  VSAM References ||  Tutorials by Drona Series ||  SQL tutorial ||  BOOKS  ||  SiteMap  ||  Expeditor Tutorial  ||  FILE-AID Tutorial  ||  Changeman Tutorial  ||  COBOL   ||  DB2   ||  JCL  ||  CICS  ||  VSAM  ||  DB2 Interview Questions ( 110 )   || Simple JCL Tutorials  || JCL Tutorial from MainframeGurukul.com   || Simple JCL Tutorial - Chapter1 ;|| Mainframe Forum - Tutorials  || Mainframe Tutorials || Mainframe CICS tutorial|| Mainframe COBOL Tutorial Drona Educational Forums - Mainframe Cobol DB2 CICS Board Powered by phpBB
mainframetopic-6764.html