IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search Log in to check your private messages Log in
 

DB2 - CURRENT DATE + 2 YRS logic


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 141

PostPosted: Thu Nov 26, 2015 3:07 am
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

Global Moderator


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

PostPosted: Thu Nov 26, 2015 3:15 am
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

Active User


Joined: 29 Jun 2005
Posts: 141

PostPosted: Thu Nov 26, 2015 3:30 am
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

Global Moderator


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

PostPosted: Thu Nov 26, 2015 3:32 am
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 

Search our Forum:

Similar Topics
Topic Forum Replies
No new posts Date Conversion Y2T to Y2W SYNCSORT 8
No new posts Date format conversion DFSORT/ICETOOL 4
No new posts Hex Value - vb file replace old date ... DFSORT/ICETOOL 14
No new posts current batch plan DB2 3
No new posts Sync logic between VSAM files and DB2... COBOL Programming 9

Back to Top