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
 

 

Regd the Date logic

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

New User


Joined: 18 Jun 2008
Posts: 54
Location: Colombo

PostPosted: Mon May 18, 2009 12:30 pm    Post subject: Regd the Date logic
Reply with quote

Hi,

My current requirement is to extract records between the particular months, i.e to extract the data from staring of the month to end of the some other month..

Example Query:
Select *
from EMP_TABLE
where EMP_DATA BETWEEN :WS-START-DATE AND :WS-END-DATE

Here i am altering the start & end dates according to the current system date..but day fields are hardcoded as '01' and '31' for WS-START-DATE AND WS-END-DATE respectively..

Since 31st Apr is not a valid date ..am getting -181 error for the above mentioned query..

How can i include a logic to overcome my problem instead of hardcoding the day based on the month?
Back to top
View user's profile Send private message

Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Mon May 18, 2009 1:31 pm    Post subject:
Reply with quote

How about using LAST_DAY(DATE) scalar function in DB2?
Back to top
View user's profile Send private message
cvijay784
Warnings : 1

New User


Joined: 18 Jun 2008
Posts: 54
Location: Colombo

PostPosted: Mon May 18, 2009 2:23 pm    Post subject:
Reply with quote

Yes..i think this LAST_DAY function will be useful for my req with little bit modification..

Ex:

Here WS-START-DATE = 2009-04-01

EXEC SQL
SET :WS-END-DATE = LAST_DAY(:WS-START-DATE)
END-EXEC.

Now WS-END-DATE = 2009-04-30

And i think for leap years also it should work to give the proper end-date

Thanks for the reply..
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Mon May 18, 2009 2:39 pm    Post subject:
Reply with quote

Welcome.... icon_smile.gif
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 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
No new posts Date and time format in CICS Chandru3183 CICS 2 Sat Mar 18, 2017 12:46 pm


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