Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Date Month Manipulation

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
HameedAli

Active User


Joined: 16 Apr 2009
Posts: 151
Location: India

PostPosted: Sun Feb 13, 2011 10:14 pm    Post subject: Date Month Manipulation
Reply with quote

Hi All
I have a table in which a column is a char field of length 20 in a table.
It contains date in first 10 positions and month in next 2 positions.
Code:

COL1     
------------
2011-01-3106

I'm trying to add the (SUBSTR(COL1,11,2) to (SUBSTR(COL1,1,10)and get the result as
Code:

COL1     
--------------------
2011-01-012011-06-31


Is it feasible to add like this? How to achieve this?
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Feb 13, 2011 10:41 pm    Post subject:
Reply with quote

Hello,

Suggest you think more about this. . .

There is no month 6 day 31. . . I suspect it would not be good to generate invalid data.
Back to top
View user's profile Send private message
Ronald Burr

Active User


Joined: 22 Oct 2009
Posts: 293
Location: U.S.A.

PostPosted: Mon Feb 14, 2011 3:37 am    Post subject:
Reply with quote

Untested:

UPDATE TABLENAME
SET COL1 = CHAR(SUBSTR(COL1,1,10)||CHAR(DATE(SUBSTR(COL1,1,10) + INT(SUBSTR(COL1,11,2)) MONTHS))
WHERE ?????;
Back to top
View user's profile Send private message
HameedAli

Active User


Joined: 16 Apr 2009
Posts: 151
Location: India

PostPosted: Mon Feb 14, 2011 10:53 am    Post subject: Reply to: Date Month Manipulation
Reply with quote

Excuse me for the confusion, it should be
Code:

COL1     
--------------------
2011-01-012011-06-30
Back to top
View user's profile Send private message
HameedAli

Active User


Joined: 16 Apr 2009
Posts: 151
Location: India

PostPosted: Mon Feb 14, 2011 12:09 pm    Post subject: Reply to: Date Month Manipulation
Reply with quote

Ronald Burr,
I tried with your query. it didn't work, Thanks for your effort
Back to top
View user's profile Send private message
HameedAli

Active User


Joined: 16 Apr 2009
Posts: 151
Location: India

PostPosted: Mon Feb 14, 2011 12:10 pm    Post subject: Reply to: Date Month Manipulation
Reply with quote

The following select clause helped me in achieving this.

Code:
SELECT DTE + MTHS MONTHS                 
 FROM  (                                 
SELECT    DATE(SUBSTR(COL1,1,10)) AS DTE ,
       INTEGER(SUBSTR(COL1,11,2)) AS MTHS
 FROM  Table1 ) AS Table2
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Mon Feb 14, 2011 2:55 pm    Post subject:
Reply with quote

talk about moving targets.

first you want to update a column: you provided expected results which showed an updated column.

next you complain about sql that was posted as untested.

then you provide a solution that does not satisfy your required results: you wanted to update a column.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10274
Location: italy

PostPosted: Mon Feb 14, 2011 3:19 pm    Post subject: Reply to: Date Month Manipulation
Reply with quote

strong warning

for date related computation ( adding subtracting days, months, years )
before worrying about the technical issue ( as sadly everybody does over here )
everybody, repeat everybody, should be concerned more about the organization standards and practices...

the issue mostly relates to last day of the month and leap year processing..
there are general practices in place,
( it is accepted that adding one month to the Jan 29/30/31 will result on feb 28/29 )
the issue should anyway be brought to the attention of the powers of Your organization
for awareness and proper legalese !
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Reg:Displaying the particular month o... bhavana yalavarthi All Other Mainframe Topics 4 Sat Jul 08, 2017 8:13 pm
No new posts How to add header with Date(YYMMDD) i... Rajan Moorthy DFSORT/ICETOOL 2 Thu Jul 06, 2017 11:44 pm
No new posts Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm
No new posts LISTIDR compiled date/time jerryte IBM Tools 3 Thu Apr 20, 2017 7:37 pm
No new posts SUBSTR for Date functions Shaheen Shaik DB2 4 Thu Apr 06, 2017 2:14 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us