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
 

 

I want to set monthenddate for previous&current month in

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

New User


Joined: 29 Mar 2006
Posts: 4

PostPosted: Wed Mar 29, 2006 3:32 pm    Post subject: I want to set monthenddate for previous&current month in
Reply with quote

I want to set month-end-dates for previous month and curren month in db2, Forexample, if the program is run on
March 11th, the processing date would be set to 2006-02-28. If the program
is run on March 17th, the processing date would be set to 2006-03-31.
Back to top
View user's profile Send private message

thanooz

New User


Joined: 28 Jun 2005
Posts: 99

PostPosted: Wed Mar 29, 2006 3:49 pm    Post subject:
Reply with quote

hi Hari Inturi,

you can set using this sql statement to previous month date

set fieldname=DATE(DAYS(CURRENT_DATE)-DAY(CURRENT_DATE))

thank's
thanooz.
Back to top
View user's profile Send private message
thanooz

New User


Joined: 28 Jun 2005
Posts: 99

PostPosted: Wed Mar 29, 2006 4:11 pm    Post subject:
Reply with quote

hi Hari Inturi,

you can set using this sql statement to current month date

set fieldname=LAST_DAY(CURRENT_DATE)


thank's
thanooz.
Back to top
View user's profile Send private message
Hari Inturi

New User


Joined: 29 Mar 2006
Posts: 4

PostPosted: Wed Mar 29, 2006 4:40 pm    Post subject: Re: I want to set monthenddate for previous&current mont
Reply with quote

My requirement is like this"Program AA3600 should set the processing date for rows with WORK_CT
equal to either 'PL DT' or to 'RW DT' based on the current date
that the program is executed on. Use the 15th
of the month as the trigger date. For example, if the program is run on
March 11th, the processing date would be set to 2006-02-28. If the program is run on March 17th, the processing date would be set to 2006-03-31?"
How can i set monthend-date with an db2 table?here work_ct is an column name of the table, PL DT and RW DT are rows?How can i set WORK_CT equal to monthend-dates with an PL DT?
Can u please write db2 code for this?
Back to top
View user's profile Send private message
thanooz

New User


Joined: 28 Jun 2005
Posts: 99

PostPosted: Wed Mar 29, 2006 4:47 pm    Post subject:
Reply with quote

hi Hari Inturi,

I am not geting please explain with example

Thank's
thanooz.
Back to top
View user's profile Send private message
Hari Inturi

New User


Joined: 29 Mar 2006
Posts: 4

PostPosted: Wed Mar 29, 2006 4:59 pm    Post subject: Re: I want to set monthenddate for previous&current mont
Reply with quote

ok, i will given complete description of of my problem
"As Is Now: Program WKPK069 reads the RSTOOK005 DB2 table and adds one month
to the processing date for rows with WORK_TC equal to either
'PL DT' or to 'RW DT'.

As Is Problem(s): There have been problems with dates being set
incorrectly, and the possibility exists that if the program is run more
than once during monthly processing, the resulting dates will be incorrect.

To Be: Program should set the processing date for rows with WORK_TC
equal to either 'PLDT' or to 'RW DT' based on the current date
that the program is executed on. So, no matter haw many times it may run
during the monthly processing, the date set will be correct. Use the 15th
of the month as the trigger date. For example, if the program is run on
March 11th, the processing date would be set to 2006-02-28. If the program
is run on March 17th, the processing date would be set to 2006-03-31."
Back to top
View user's profile Send private message
thanooz

New User


Joined: 28 Jun 2005
Posts: 99

PostPosted: Wed Mar 29, 2006 6:32 pm    Post subject:
Reply with quote

Firt you get the day from system


select day(current_date) into :hostvariable from sysibm.sysdummy1;

then write if condition,


If hostvariable <= 15

exec sql
update tablename

set fieldname=DATE(DAYS(CURRENT_DATE)-DAY(CURRENT_DATE))

wher condition

end-exec

else

exec sql
update tablename

set fieldname=LAST_DAY(CURRENT_DATE)

wher condition
end-exec


Thank's
thanooz.
Back to top
View user's profile Send private message
Hari Inturi

New User


Joined: 29 Mar 2006
Posts: 4

PostPosted: Wed Mar 29, 2006 6:44 pm    Post subject: Re: I want to set monthenddate for previous&current mont
Reply with quote

Hi Thanooz,

I will include this logic in my program.if it will work fine then no problem else i will ask u again.
Thanks for your Information.

Thanks
Hari
Back to top
View user's profile Send private message
thanooz

New User


Joined: 28 Jun 2005
Posts: 99

PostPosted: Fri Mar 31, 2006 4:54 pm    Post subject:
Reply with quote

hi Hari Inturi,

If any problem came post to me.


thank's
thanooz.
Back to top
View user's profile Send private message
babu_hi

New User


Joined: 11 Apr 2006
Posts: 93

PostPosted: Wed Apr 12, 2006 5:16 pm    Post subject:
Reply with quote

it's working fine.
Thanks
hari
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
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
No new posts How to get previous month in SYMNAMES Suganya87 SYNCSORT 9 Fri Oct 28, 2016 1:37 pm
This topic is locked: you cannot edit posts or make replies. Get correct date and time when curren... balaji81_k DB2 24 Fri Oct 14, 2016 10:40 pm
No new posts sort with previous record anatol DFSORT/ICETOOL 9 Thu Oct 06, 2016 2:36 am
No new posts Retrieve current year or month using ... vnktrrd DFSORT/ICETOOL 15 Tue Mar 15, 2016 4:14 pm


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