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
 
-181 error in Date Field in Db2 Table

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

New User


Joined: 11 Jun 2007
Posts: 2
Location: Chennai

PostPosted: Mon Jun 11, 2007 7:15 pm    Post subject: -181 error in Date Field in Db2 Table
Reply with quote

Hi,
my requirement is to extract records between the particular month, But it show error - 181, details below


Query:
Select *
from EMP_TABLE
where EMP_DATA BETWEEN :WS-START-DATE AND :WS-END-DATE

EMP_DATA IS DATE Function ( but has only dd,mm,yyyy) no timestamp.
exp: Value like be 2007-06-01

Working storage section :
WS-START-DATE PIC X(10).
WS-END_DATE PIC X(10).
WS-CURRENT_TIMESTAMP PICX(26).
Procedure division:

EXEC SQL
SET :WS-CURRENT-TIMESTAMP = CURRENT TIMESTAMP
END-EXEC

MOVE WS-CURRENT-TIMESTAMP(1:8) TO WS-START-DATE(1:8)
MOVE '01' TO WS-START-DATE(9:2)
MOVE WS-CURRENT-TIMESTAMP(1:8) TO WS-END-DATE(1:8)
MOVE '31' TO WS-END-DATE(9:2)

I get the current month from the CURRENT Time Stamp. but for Date i mention as 1 to 31 since all the record should be between 1 to 31 for every month, But the problem is for June month it show error -181 since it does not have 31 as date ,

Please advise how can i specify the date for selecting records for month end Jobs, Since the job is schedule as last day of the Month.

Thanks in advance.
Back to top
View user's profile Send private message

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Mon Jun 11, 2007 7:22 pm    Post subject: Re: -181 error in Date Field in Db2 Table
Reply with quote

rsankar wrote:
Hi,
my requirement is to extract records between the particular month, But it show error - 181, details below


Query:
Select *
from EMP_TABLE
where EMP_DATA BETWEEN :WS-START-DATE AND :WS-END-DATE

EMP_DATA IS DATE Function ( but has only dd,mm,yyyy) no timestamp.
exp: Value like be 2007-06-01

Working storage section :
WS-START-DATE PIC X(10).
WS-END_DATE PIC X(10).
WS-CURRENT_TIMESTAMP PICX(26).
Procedure division:

EXEC SQL
SET :WS-CURRENT-TIMESTAMP = CURRENT TIMESTAMP
END-EXEC

MOVE WS-CURRENT-TIMESTAMP(1:8) TO WS-START-DATE(1:8)
MOVE '01' TO WS-START-DATE(9:2)
MOVE WS-CURRENT-TIMESTAMP(1:8) TO WS-END-DATE(1:8)
MOVE '31' TO WS-END-DATE(9:2)

I get the current month from the CURRENT Time Stamp. but for Date i mention as 1 to 31 since all the record should be between 1 to 31 for every month, But the problem is for June month it show error -181 since it does not have 31 as date ,

Please advise how can i specify the date for selecting records for month end Jobs, Since the job is schedule as last day of the Month.

Thanks in advance.

Code:

EXEC SQL
SET :WS-END-DATE = LAST_DAY(DATE(:WS-START-DATE))
END-EXEC
Back to top
View user's profile Send private message
rsankar

New User


Joined: 11 Jun 2007
Posts: 2
Location: Chennai

PostPosted: Mon Jun 11, 2007 7:42 pm    Post subject:
Reply with quote

One more question is

I m using Between for Date Then why it showing error for 31 in June , Whatever may be date it should fetch the records.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Mon Jun 11, 2007 7:47 pm    Post subject:
Reply with quote

rsankar wrote:
One more question is

I m using Between for Date Then why it showing error for 31 in June , Whatever may be date it should fetch the records.


31 June is not a valid date! When you use dates in SQL they must be valid dates. icon_mad.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Jun 11, 2007 8:10 pm    Post subject:
Reply with quote

Good catch icon_smile.gif
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 IEW2456E error when link-editing a C ... Senthilraj JCL & VSAM 0 Fri Oct 13, 2017 3:12 pm
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm
No new posts Partial color change of a field in CI... waseem0424 CICS 5 Fri Sep 29, 2017 7:56 pm

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