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
 

 

SQLCODE -180 Date Error

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

New User


Joined: 05 Jan 2008
Posts: 50
Location: Olympia, WA, USA

PostPosted: Fri Sep 05, 2008 1:28 am    Post subject: SQLCODE -180 Date Error
Reply with quote

Hi all,

Could anyone please help in this regard.
Thanks in Advance

I would like to know how to get the data of 5 yrs from a particular date.

I am using the followin query, but getting an error.
SELECT *
FROM
LEXUS.TLCS005
WHERE RID IN
(SELECT RID
FROM LEXUS.TLCS007
WHERE RDNDTE IN
(SELECT RDNDTE
FROM LEXUS.TLCS003
WHERE DATE(RDNDTE) >= DATE('2008/07/23') - 5 YEARS))
WITH UR;


ERROR:

Code:
   RID   OPSEQ  OPCODE      OPCOMB  OPHOURS     OPTECHID  OPDESC       
---------+---------+---------+---------+---------+---------+---------+------
DSNT408I SQLCODE = -180, ERROR:  THE DATE, TIME, OR TIMESTAMP VALUE *N IS   
         INVALID                                                           
DSNT418I SQLSTATE   = 22007 SQLSTATE RETURN CODE                           
DSNT415I SQLERRP    = DSNXRTIM SQL PROCEDURE DETECTING ERROR               
DSNT416I SQLERRD    = -6805 0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION       
DSNT416I SQLERRD    = X'FFFFE56B'  X'00000000'  X'00000000'  X'FFFFFFFF'   
         X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION               
---------+---------+---------+---------+---------+---------+---------+----
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Fri Sep 05, 2008 1:37 am    Post subject: Reply to: SQLCODE -180 DATE ERROR
Reply with quote

what is the data-type of RDNDTE?

does your site use backslash / , or hypen - as separator?
is yours a year/month/day system?

answer those three questions, and you will solve the problem, or at least know if it is RDNDTE or your literal.
Back to top
View user's profile Send private message
satyender

New User


Joined: 05 Jan 2008
Posts: 50
Location: Olympia, WA, USA

PostPosted: Fri Sep 05, 2008 3:55 am    Post subject:
Reply with quote

Thanks dbzTHEdinosauer

I have resoved by converting into days. Now its working fine.
Thanks for your quick response.

Code:
SELECT *                                                       
FROM                                                           
LEXUS.TLCS005                                                   
WHERE RID IN                                                   
  (SELECT RID                                                   
    FROM LEXUS.TLCS007                                         
        WHERE RDNDTE IN                                         
       (SELECT RDNDTE                                           
            FROM LEXUS.TLCS003                                 
              WHERE DATE(RDNDTE) >= CURRENT DATE - 1868 DAYS   
              AND DATE(RDNDTE) <= CURRENT DATE - 42 DAYS))     
 WITH UR;
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Fri Sep 05, 2008 5:05 am    Post subject:
Reply with quote

now that you have converted the literal to the db2 current_date.
you probably could go back to the 5 years.

is any five year span always = 1868 days?

due to some recent experience I had, I would also suggest changing the left hand predicate
From: DATE(RDNDTE) TO: RDNDTE
and
CAST the date returned from
<CURRENT_DATE minus whatever>
to what ever data type attribute the column RDNDTE has- is.

you would then reduce the resource requirement by
not executing a function on every row
DATE(RDNDTE)
, and instead, only once:
to derive the right-hand predicates.
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 Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm
No new posts Getting error while opening a variabl... apandey1 COBOL Programming 5 Fri May 05, 2017 12:22 pm
No new posts LISTIDR compiled date/time jerryte IBM Tools 3 Thu Apr 20, 2017 7:37 pm
No new posts SUBSTR for Date functions Shaheen Shaik DB2 4 Thu Apr 06, 2017 2:14 pm
No new posts IDC2902I Error tzeche ABENDS & Debugging 2 Tue Mar 28, 2017 11:39 am


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