CODEWITHSUNDEEP
db2db2-400
Dipanshu Shekhar
Dipanshu Shekhar

Reputation: 45

Facing issue in DB2 Sql error with SQLCODE=-181, SQLSTATE=22007, SQLERRMC=0;*N, DRIVER=3.61.75

I am facing an issue while using date formatting in where clause, while the same formatting works fine for another select query.

Working query using following condition in where clause:

select t1.x,t1.y,t2.z 
  from t1 
    inner join t2 
  where
    TIMESTAMP(SUBSTR(20||t1.TRANSACTION_DATE,1,4)||'-'||SUBSTR(t1.TRANSACTION_DATE,3,2)||'-'||SUBSTR(t1.TRANSACTION_DATE,5,2)||' '||SUBSTR(t1.TRANSACTION_TIME,1,2)||':'||SUBSTR(t1.TRANSACTION_TIME,3,2)||':'||SUBSTR(t1.TRANSACTION_TIME,5,2))
      BETWEEN '2018-06-01 00:00:00' AND '2018-06-18 12:01:00';

When the same query is used for t1 table and t3 table like:

select t1.x,t1.y,t3.z 
  from t1 
    inner join t3 
  where
    TIMESTAMP(SUBSTR(20||t1.TRANSACTION_DATE,1,4)||'-'||SUBSTR(t1.TRANSACTION_DATE,3,2)||'-'||SUBSTR(t1.TRANSACTION_DATE,5,2)||' '||SUBSTR(t1.TRANSACTION_TIME,1,2)||':'||SUBSTR(t1.TRANSACTION_TIME,3,2)||':'||SUBSTR(t1.TRANSACTION_TIME,5,2))
      BETWEEN '2018-06-01 00:00:00' AND '2018-06-18 12:01:00';

It does not work for the timestamp part.

Note: Transaction_date value is in '180618' format(yymmdd) in the table t1. Also the transaction_time is in 123030(hhmmss) format

Upvotes: 0

Views: 5721

Answers (1)

jmarkmurphy
jmarkmurphy

Reputation: 11493

Your Timestamp values have an error. You are trying to calculate the timestamp of 201806-06-18 12:30:30. That just won't work.

Change SUBSTR(20||t1.TRANSACTION_DATE,1,4) to SUBSTR(20||t1.TRANSACTION_DATE,1,2) in each query.

or you could replace that whole long substring with 

timestamp_format(digits(t1.transaction_date) || digits(t1.transaction_time), 'YYMMDDHH24MISS')

Upvotes: 2

Related Questions