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: 86

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: 1609
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: 86

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: 1609
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 TOT & DATE parameter in ICEMAN hemanthj642 DFSORT/ICETOOL 4 Mon Nov 14, 2016 5:19 am
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
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 Change date (DD/MM/YY) in 2nd record ... uday kiran DFSORT/ICETOOL 12 Wed Sep 07, 2016 10:57 pm
No new posts Wildcard logic in COBOL dudenithy COBOL Programming 13 Thu Sep 01, 2016 7:36 pm


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