Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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

Active User


Joined: 29 Jun 2005
Posts: 118

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

Active User


Joined: 29 Jun 2005
Posts: 118

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: 1806
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 Compare yesterday's date to the one o... migusd SYNCSORT 11 Fri Sep 22, 2017 11:35 pm
No new posts Comparing 2 Files using Current time arunsoods SYNCSORT 9 Fri Sep 22, 2017 6:00 pm
No new posts Validate the Date girishb2 DFSORT/ICETOOL 9 Tue Sep 19, 2017 1:12 am
No new posts Julian Date to CICS ABSTTIME blayek CICS 3 Wed Aug 30, 2017 11:15 pm
No new posts How to add header with Date(YYMMDD) i... Rajan Moorthy DFSORT/ICETOOL 2 Thu Jul 06, 2017 11:44 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us