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
 
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: 6968
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: 6968
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 IEW2456E error when link-editing a C ... Senthilraj JCL & VSAM 0 Fri Oct 13, 2017 3:12 pm
No new posts FTP Skip or ignore error and process ... AJAYREDDY All Other Mainframe Topics 3 Wed Sep 27, 2017 8:12 pm
No new posts Assembler Macro Copybook converted to... Ziquilix PL/I & Assembler 4 Tue Sep 26, 2017 3:07 am
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

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