SQLSTATE=42703

Hi bobby2929,

A couple of ways.  Perhaps the easiest is to treat the list as a subquery.

SELECT * FROM
(
  select  ICRTDT ,
      substr (cast (int( ICRTDT)+20000000 as char (8)), 1, 4) || '-' ||
      substr (cast (int( ICRTDT)+20000000 as char (8)), 5, 2) || '-' ||
      substr (cast (int( ICRTDT)+20000000 as char (8)), 7, 2) as NEWICRTDT
   from v_aaa_item
) AS a
where NEWICRTDT = '2001-06-07'
fetch first 10 rows only;


If performance is an issue, you can also repeat the computing in the WHERE clause.

select  ICRTDT ,
    substr (cast (int( ICRTDT)+20000000 as char (8)), 1, 4) || '-' ||
    substr (cast (int( ICRTDT)+20000000 as char (8)), 5, 2) || '-' ||
    substr (cast (int( ICRTDT)+20000000 as char (8)), 7, 2) as NEWICRTDT
   from v_aaa_item
where '2001-06-07' =
    substr (cast (int( ICRTDT)+20000000 as char (8)), 1, 4) || '-' ||
    substr (cast (int( ICRTDT)+20000000 as char (8)), 5, 2) || '-' ||
    substr (cast (int( ICRTDT)+20000000 as char (8)), 7, 2)
fetch first 10 rows only;


Note however, that your math appears off.  You probably don't want to add 20000000 to the month and day values.



Good Luck!
Kent

what is the underlying data type of ICRTDT?

and what are you attempting to do?

wouldn't using the DATE functions... be more appropriate?
DATE/TIMESTAMP/DAY/MONTH/YEAR ...

which version and O/S of DB2/UDB are you using?

ICRTDT IS IN DECIMAL
your rite date format should be appropriate for NEWICRTDT
how can i do cast in date format ?
thanks
bobby

Select  ...
           ,Date(substr(newicrtdt,7,2) concat '.' concat substr(newicrtdt,5,2) concat '.' concat substr(newicrtdt,1,4)) as newicrtdt
   from (
select a.* ,
         cast(icrtdt as char(8)) as newicrtdt
  from v_aaa_item
 ) as x  
where neticrtdt = '2001-06-07'
fetch first 10 rows only

HI KDO
its version 8.2
SELECT * FROM
(
  select ICRTDT,
    cast (
      (cast ((ICRTDT/10000+2000) as char(4)) || '-' ||
      cast (MOD(ICRTDT/100, 100) as char(2)) || '-' ||
      cast (MOD(ICRTDT, 100) as char(2))) as date) as NEWICRTDT
  from v_aaa_item
) AS a
where NEWICRTDT = '2001-06-07'
fetch first 10 rows only;
------------------------------------------------------------------------------
SELECT * FROM ( select ICRTDT, cast ( (cast ((ICRTDT/10000+2000) as char(4)) || '-' || cast (MOD(ICRTDT/100, 100) as char(2)) || '-' || cast (MOD(ICRTDT, 100) as char(2))) as date) as NEWICRTDT from v_aaa_item ) AS a where NEWICRTDT = '2001-06-07' fetch first 10 rows only
SQL0440N  No authorized routine named "MOD" of type "FUNCTION" having
compatible arguments was found.  SQLSTATE=42884
Thanks,
Bobby

MOD() expects two integer parameters.  The fact that it's failing suggests that ICRTDT is not an integer type.

How is ICRTDT defined?


Kent

ICRTDT IS DECIMAL

MOD() should convert the decimal to integer.  weird.

Just for grins, try this one:


SELECT * FROM
(
  select ICRTDT,
    cast (
      (cast ((ICRTDT/10000+2000) as char(4)) || '-' ||
      cast (MOD(INT(ICRTDT/100), 100) as char(2)) || '-' ||
      cast (MOD(INT(ICRTDT), 100) as char(2))) as date) as NEWICRTDT
  from v_aaa_item
) AS a
where NEWICRTDT = '2001-06-07'
fetch first 10 rows only;

-----------------------------------------------------------------------------
SELECT * FROM ( select ICRTDT, cast ( (cast ((ICRTDT/10000+2000) as char(4)) || '-' || cast (MOD(INT(ICRTDT/100), 100) as char(2)) || '-' || cast (MOD(INT(ICRTDT), 100) as char(2))) as date) as NEWICRTDT from v_aaa_item ) AS a where NEWICRTDT = '2001-06-07' fetch first 10 rows only

ICRTDT    NEWICRTDT
--------- ----------
SQL0180N  The syntax of the string representation of a datetime value is
incorrect.  SQLSTATE=22007

SQL0180N  The syntax of the string representation of a datetime value is incorrect.

Explanation:

The string representation of a date, time, or timestamp value
does not conform to the syntax for the specified or implied data
type.  

 The statement cannot be processed.  

User Response:

Ensure that the syntax of the date, time, or timestamp value
conforms to the syntax for its data type.  If the string is not
intended to be a date, time, or timestamp value, ensure that when
used, it does not imply that data type.  

 Federated system users: the problem might be due to a date/time
representation problem at the data source.  If the reason is
unknown, isolate the problem to the data source failing the
request problem determination guide and examine the date/time
representation restrictions for that data source.  

 sqlcode :  -180

 sqlstate :  22007