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
 
is '0001-01-01' a valid date in DB2

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

New User


Joined: 03 Dec 2007
Posts: 58
Location: Bangalore

PostPosted: Thu Nov 29, 2012 12:27 pm    Post subject: is '0001-01-01' a valid date in DB2
Reply with quote

Hi,

I have the sql in Cobol DB2 program which gives -304 as SQLCODE

Code:
EXEC SQL                                               
     SET :VAR1   =                           
         DATE (CURRENT DATE) - DATE (:VAR2) 
END-EXEC         


Where VAR2 has a value '0001-01-01'. The above query errors out with Sqlcode -304.

If i execute the same query in SPUFI, am getting the result. It is not erroring out.

Code:
SELECT                                             
   DATE (CURRENT_DATE) - DATE ('0001-01-01')       
   FROM SYSIBM.SYSDUMMY1;     


I checked in net and few forums say that '0001-01-01' is a valid date in DB2. Am not sure then why my Cobol Program errors out with SQLCODE -304 when i feed the date as '0001-01-01'

Thanks
Sikkandhar
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1542
Location: Andromeda Galaxy

PostPosted: Thu Nov 29, 2012 1:13 pm    Post subject:
Reply with quote

Code:
---------+---------+---------+---------+---------+---------+---------+---------+
SELECT                                                                  00000199
   DATE (CURRENT DATE) - DATE ('0001-01-01')                            00000299
   FROM SYSIBM.SYSDUMMY1;                                               00000399
---------+---------+---------+---------+---------+---------+---------+---------+

---------+---------+---------+---------+---------+---------+---------+---------+
 20111028,

Is the output you get

What is the business reason here at first place to subtract a year , a month and a day from a given date??

Can you try ?

Code:
SELECT                                             
   DATE (CURRENT_DATE) - DATE ('0001-01-01')   
   INTO :VAR1   
   FROM SYSIBM.SYSDUMMY1; 
Back to top
View user's profile Send private message
Sikkandhar

New User


Joined: 03 Dec 2007
Posts: 58
Location: Bangalore

PostPosted: Thu Nov 29, 2012 2:13 pm    Post subject:
Reply with quote

The Input date should not exceed specific limit when compared with the Current date.
In one of the cases, the Input date fed was '0001-01-01'. This date should have ideally returned 20111028 (which is beyond a specific limit as per Business Rules). Instead it returns SQLCODE -304

Tried with below code also

Code:
SELECT                                             
    DATE (CURRENT DATE) - DATE (:VAR2) 
INTO :VAR1                             
FROM SYSIBM.SYSDUMMY1         


Where VAR2 has the value '0001-01-01'.
It is still giving me SQLCODE -304
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Nov 29, 2012 2:47 pm    Post subject:
Reply with quote

Sikkandhar,

Read the Error message properly.

Have you verified the table definations.
Quote:
Programmer Response: Verify that table definitions are current, and that
the host variable has the correct data type. See the explanation for
SQLCODE -405 for ranges of SQL data types.


Show us how the :VAR1 is defined in working-storage section.

Thanks,
Sushanth
Back to top
View user's profile Send private message
Sikkandhar

New User


Joined: 03 Dec 2007
Posts: 58
Location: Bangalore

PostPosted: Thu Nov 29, 2012 2:56 pm    Post subject:
Reply with quote

Thanks Sushanth.

I was able to resolve the error. I had defined VAR1 as S9(04) comp. The parameter that was about to be set is beyond the range. Changed the declaration to S9(09) comp and it worked fine
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 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
No new posts Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm

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