| View previous topic :: View next topic |
| Author |
Message |
ace
Beginner
Joined: 24 Feb 2004
Posts: 58
Topics: 23
|
 Posted: Wed Feb 25, 2004 11:49 am Post subject: Convert date format |
 |
|
Hi,
I want to convert the date filed from my input file which is mm/dd/yy format to mm/dd/ccyy in my COBOL-DB2 program. How can I use
DB2 for doing so?
_________________
Thank You
-Ace |
|
| Back to top |
|
 |
vini
Intermediate
Joined: 12 Jan 2004
Posts: 240
Topics: 48
Location: Maryland
|
| Posted: Wed Feb 25, 2004 2:51 pm Post subject: |
|
|
Ace ,
Why would you want to use DB2 for that ?!! I dont think you can get that from db2 ..
Reminds me of y2k days .. isnt your program or system y2k compliant ? 
However if its like a one-time effort and all your CC in the Dates is a Constant ...in this example '20' then you can do it either in Cobol or in File Edit mode.
In Cobol ..
Define Data items as Follows in working storage
| Code: |
10 DATE-IN-FILE.
20 MONTH PIC X(2).
20 FILLER PIC X(1).
20 DAY PIC X(2).
20 FILLER PIC X (1).
20 YEAR PIC X(2).
10 DATE-OUT.
20 MONTH PIC X(2).
20 FILLER PIC X(1) VALUE '/'.
20 DAY PIC X(2).
20 FILLER PIC X (1) VALUE '/'.
20 CENTURY PIC X(2) VALUE '20'.
20 YEAR PIC X(2).
In Procedure Division
MOVE FILE-DATE TO DATE-IN-FILE
MOVE CORRESPONDING DATE-IN-FILE TO DATE-OUT
|
|
|
| Back to top |
|
|
ace
Beginner
Joined: 24 Feb 2004
Posts: 58
Topics: 23
|
| Posted: Wed Feb 25, 2004 3:27 pm Post subject: |
|
|
Hi Vini,
Appreciate your effort. 
I'm aware of Y2K and window logic. If I want to use a COBOL solution I wouldn't have post this question here. That is the reason I asked specifically
| Quote: |
How can I use DB2 for doing so?
|
_________________
Thank You
-Ace |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Mon Mar 01, 2004 2:08 pm Post subject: |
|
|
Ace,
You can convert the two digit year to a four digit year using a case statement. In your case let us say you moved to the date(mm/dd/yy) from the file to a working storage field named IN-DATE which is pic x(08 ). Now you want to convert the year to a 4 digit year.This sql will expand the year portion to 4 digit year using the window logic.If the year is GE 50 and LT 100 , the century field is padded with 19 else it is padded with 20.so depending on the window you can change the values in the case statement.
| Code: |
SELECT SUBSTR(IN-DATE,1,6) CONCAT
(CASE SUBSTR(IN-DATE,7,1)
WHEN '9' THEN '19'
WHEN '8' THEN '19'
WHEN '7' THEN '19'
WHEN '6' THEN '19'
WHEN '5' THEN '19'
ELSE '20'
END) CONCAT
SUBSTR(IN-DATE,7,2)
FROM SYSIBM.SYSDUMMY1
;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
somuk
Beginner
Joined: 04 Feb 2003
Posts: 113
Topics: 37
|
| Posted: Tue Mar 02, 2004 1:53 pm Post subject: |
|
|
Thanks Kolusu.
I was also looking for the same requirement.
Because of your absence I have to wait for a week to get an answer.
_________________
Regds,
Somu |
|
| Back to top |
|
|
sri_naveen
Beginner
Joined: 29 Oct 2003
Posts: 10
Topics: 0
Location: Indore, India
|
| Posted: Wed Mar 03, 2004 5:30 am Post subject: |
|
|
Hi ace,
I had a hearty laugh at this stuff posted....
Lemme tell u one thing in programming. Anything can be done with anything. I can code an entire prime number logic or weather forecasting in DB2.... but the catch is how optimised DB2 is to do it..
If you convert the date by the method Kolusu suggests, its a mockery of DB2... leave alone optimised queries, we arent getting to what queries are actually....
Vini is right in the sense...a procedural language is the best place to do a date format change...or else use the standard date conversion utils in DB2 coz... they are written down by the system programmers who have optimised it for that environment.
You still have to convince me what forces you to wanna realise this logic in DB2..!! Your call.......
_________________
Regards,
Naveen Srinivasan
Computer Sciences Corporation
--To err is human, to err again is more human-- |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Wed Mar 03, 2004 7:04 am Post subject: |
|
|
Naveen,
| Quote: |
I had a hearty laugh at this stuff posted....
If you convert the date by the method Kolusu suggests, its a mockery of DB2... leave alone optimised queries, we arent getting to what queries are actually....
|
I am glad you had a hearty laugh, but the question here is valid and it is not that stupid as you think.
Btw the solution is not a mockery of DB2 but an effective use of the features of DB2.
Let me give you a scenario where coding the same logic in a program is complicated.
I have a table which has column defined as CHAR with a length of 12 bytes and the column is used to store a constant 4 bytes + date in the format MM-DD-YY.
| Code: |
ABCD01-01-00
EFGH01-01-99
IJKL01-01-01
ZZZZ01-01-02
LLLL01-01-98
|
I have declared a cursor. I need to sort the dates in ascending order. So now tell me how can you do it in a program.?
You need to perform the following steps:
1. fetch the cursor till the end and load it into an internal table while expanding the year using the window logic
2. code your own sort logic to sort the records or call an external sort to perform the task for you.
So now tell me how much work is involved in here?
on the other hand with DB2 , it is just one query with an order by cluase.
| Quote: |
I can code an entire prime number logic or weather forecasting in DB2...
|
I would love to see your weather forecasting sql.
I am posting this not to defend my solution but to suggest not to make fun of posts.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
sri_naveen
Beginner
Joined: 29 Oct 2003
Posts: 10
Topics: 0
Location: Indore, India
|
| Posted: Wed Mar 03, 2004 7:24 am Post subject: |
|
|
Hi kolusu..
haha... I wasnt poking fun.. Maybe I wasn't interpreted properly.
In your example with a cursor, I wonder why you need to do all those steps. You can always find cases where an approach can be justified. But for this particular posting, doing it in COBOL is the most judicious approach. You cannot just decide to use a cursor like that.. There are options to weigh. You got to apply the strengths of an approach.
And yes.. I do not believe in "quoting n replying".. coz thats wat I call making fun of posts...
Take it in the right sense... I have never said what u say is wrong. Doin it in a better way helps... In the industry, the overhead also matters...
_________________
Regards,
Naveen Srinivasan
Computer Sciences Corporation
--To err is human, to err again is more human-- |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Wed Mar 03, 2004 8:46 am Post subject: |
|
|
Sri_Naveen,
I agree that for this particular posting that the cobol code is efficient. I was merely providing the solution how it can be done in DB2.
| Quote: |
In your example with a cursor, I wonder why you need to do all those steps.
|
let me put the requirement clearly. As I have the column mentioned in my previous post and table has several other columns.
I want to update a few columns say 8 columns in that table based on the dates in the column described earlier. At any point you will have min of 1000 rows to a max of 50,000 rows which qualify for the update.
So now tell me how do you solve this.
Approach 1:
Unload the table and create the update file running thru pgm (cobol, easytrieve ) or an utility
Reload back the updated rows only ( you need to delete the old rows first before you can load resume)
Approach 2:
code a cobol pgm to update the table using a cursor
Any other approach you can think of
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
sri_naveen
Beginner
Joined: 29 Oct 2003
Posts: 10
Topics: 0
Location: Indore, India
|
| Posted: Wed Mar 03, 2004 9:14 am Post subject: |
|
|
Oh no..not again... I think you are obsessed with DB2 ... 
In this case, updating the table (with a hazaar rows) , cursors are indeed the best bet. But can't we just recall what the issue was ? It was converting a Date Format.. and since its an attribute change(doesnt depend at all on the population of the table), COBOL scores over DB2....
Your method is right for your problem. You neednt even bother writing an EasyTrieve for it.
Look at it... as Ace says...
| Quote: |
If I want to use a COBOL solution I wouldn't have post this question here
|
The point I want to drive home is why you "should want" a COBOL solution or whatever you call it...
_________________
Regards,
Naveen Srinivasan
Computer Sciences Corporation
--To err is human, to err again is more human-- |
|
| Back to top |
|
|
ace
Beginner
Joined: 24 Feb 2004
Posts: 58
Topics: 23
|
| Posted: Wed Mar 03, 2004 5:17 pm Post subject: |
|
|
Firstly thanks to Kolusu for the solution.
And to Naveen..
If you see my first posting you better understand what I'm looking for.
And If you are asking why, I don't have to put down the complete requirement in this forum . Instead of making fun of others try to give some solution(if you can  ) or ask for more clarification in a better way.
BTW
I have a requirement here to write a weather forecasting query in DB2. Would appreciate any help on this. 
_________________
Thank You
-Ace |
|
| Back to top |
|
|
sri_naveen
Beginner
Joined: 29 Oct 2003
Posts: 10
Topics: 0
Location: Indore, India
|
| Posted: Wed Mar 03, 2004 11:34 pm Post subject: |
|
|
hi ace...
There is a paper in CMU coming up on AI support for DB2 There is a seperate predictive query routine designed for this purpose. If you still cant map forecasting to that, forget it.
Remember one thing... in CS, nothing is impossible coz all you have is a template. We do not accept it, which is a basic flaw in our mindset. Have it your way, then and temme what all conversions you pull out from queries !!!
| Quote: |
And If you are asking why, I don't have to put down the complete requirement in this forum
|
And BTW, in future if anyone asks a question, first try to ask why and then answer it. We are not machines..are we ??!!
_________________
Regards,
Naveen Srinivasan
Computer Sciences Corporation
--To err is human, to err again is more human-- |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Thu Mar 04, 2004 6:52 am Post subject: |
|
|
Sri_naveen,
| Quote: |
And BTW, in future if anyone asks a question, first try to ask why and then answer it. We are not machines..are we ??!!
|
If we look at every question as to why it needs to done rather than finding how we can do it , we would not have learnt any thing new at all.
Sometimes it is the sheer enthusiasm which drives the person to see how it can be done in other languages or databases.
In your very first post you claimed that you can code a weather forecasting in DB2. I already requested you to post the query, but I guess your answer would " why would you need to do a weather forecasting on mainframe when you have the world wide web at click of a mouse?"
But still I would like to know how it can be done is DB2. Just to see complexity of the sql.
You may be an optimaztion guru but you also need a creative mindset.
| Quote: |
in future if anyone asks a question, first try to ask why and then answer it.
|
I guess we do not need a helpboard in that case, as co-worker/friend of the poster can ask the poster " why do you need to do like that?"
kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
ace
Beginner
Joined: 24 Feb 2004
Posts: 58
Topics: 23
|
| Posted: Thu Mar 04, 2004 10:11 am Post subject: |
|
|
Hi Sri_Naveen,
I have seen your reply for this post. http://www.mvsforums.com/helpboards/viewtopic.php?t=1603.
Brilliant !!! 
And try to read this (Kolusu's signature) whenever you get time.
| Code: |
Treat people as if they were what they ought to be, and you helped them become what they are capable of being. --- Johann W. Von Goethe
|
_________________
Thank You
-Ace |
|
| Back to top |
|
|
vini
Intermediate
Joined: 12 Jan 2004
Posts: 240
Topics: 48
Location: Maryland
|
| Posted: Thu Mar 04, 2004 12:41 pm Post subject: |
|
|
Ace, Some of us were genuinely interested in knowing from you why you wanted to or needed to use Db2 solution for this ...
Dont say in reply that kolusu has already explained that part of it ..as those were imagined scenarios on your behalf and may not have really been the cause of your posting the question. If they were you so far didnt hint or concur on that either. If it was just your enthusiasm ..had you mentioned that in a couple of words in the beginning this post would perhaps hav not gotten this off track  |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
