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
 

 

DB2 - CURRENT DATE + 2 YRS logic

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

New User


Joined: 29 Jun 2005
Posts: 89

PostPosted: Thu Nov 26, 2015 3:07 am    Post subject: DB2 - CURRENT DATE + 2 YRS logic
Reply with quote

Hi ,

I have to insert two date fields(EFFECTIVE & EXPIRATION DATE) in a table in which effective date will be current date and expiration is of 2 yrs exactly from that date . I know we can do in SELECT SQL statement by number of days adding to current date but if in between if leap year falls then we need to have 1 more day extra . I can check for successive years for leap year check thru COBOL but is there anything i can handle in SQL query itself
can any one guide me .

[/code]
Code:


SELECT CURRENT_DATE + 730 or 731 DAYS
FROM TEST_TABLE
WITH UR ;
Back to top
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1717
Location: NY,USA

PostPosted: Thu Nov 26, 2015 3:15 am    Post subject:
Reply with quote

Is that what you want?
Code:
  select current date + 2 years from sysibm.sysdummy1
Back to top
View user's profile Send private message
balaji81_k

New User


Joined: 29 Jun 2005
Posts: 89

PostPosted: Thu Nov 26, 2015 3:30 am    Post subject: Reply to: DB2 - CURRENT DATE + 2 YRS logic
Reply with quote

Yes, I need the same . I believe it calculate from system date which in turns handle leap year days too .
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1717
Location: NY,USA

PostPosted: Thu Nov 26, 2015 3:32 am    Post subject:
Reply with quote

yes,
Code:
   select
   CASE (mod(year(current date), 400)) WHEN 0 THEN 366
          ELSE
              CASE (mod(year(current date), 4))   WHEN 0 THEN
                    CASE (mod(year(current date), 100)) WHEN 0 THEN
                     365 ELSE 366 END
        ELSE 365 END
        end
        end
        from sysibm.sysdummy1

This should give you days and handle leap year and can be used in your original post if you don't want above.
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 Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm
No new posts SMTP the current generation number of... Jyothi Kulunde JCL & VSAM 4 Thu May 04, 2017 4:08 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