Convert CHAR to TIMESTAMP in DB2 iSeries

Question

I've googling about how to convert CHAR into TIMESTAMP in DB2 iSeries with no luck, can u help me ?

Eg: 20120216

Expected Result : 16/2/2012 12:00:00 AM

MY UDF :

CREATE FUNCTION TEST.CONVERT_TO_TIMESTAMP (VAL CHARACTER VARYING(20))
RETURNS TIMESTAMP
LANGUAGE SQL
SPECIFIC TEST.CONVERT_TO_TIMESTAMP
MODIFIES SQL DATA
CALLED ON NULL INPUT
FENCED
DISALLOW PARALLEL
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE SQLCODE INTEGER DEFAULT 0 ;
DECLARE RETCODE INTEGER DEFAULT 0 ;
DECLARE RET TIMESTAMP ;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION , SQLWARNING , NOT FOUND
BEGIN
SET RETCODE = SQLCODE ;
END ;

IF ( VAL IS NOT NULL ) THEN
SET RET = CAST ( VAL AS TIMESTAMP ) ;
 ELSE
SET RET = NULL ;
  END IF ;

 RETURN RET ;

 END 
 GO

--

And the result is null :(

Any Suggestion ?

Thanks MRizq

Solution 1

In order to CAST as a timestamp, the value needs to be formatted as a valid date. '20120216' is not quite there, it needs dashes: '2012-02-16'. If you can't have the caller reformat the input you'll have to do it yourself along these lines:

cast(date(substr(val,1,4) concat '-' 
   concat substr(val,5,2) concat '-' 
   concat substr(val,7,2)) as timestamp)

Solution 2

I know this is already answered, but the following demonstrates an arguably cleaner and more direct approach.

Supposing the CHAR value you want to convert is '20140101' then you can do either:

select date('20140101'||'000000') from sysibm.sysdummy1;

or

select timestamp('20140101'||'000000') from sysibm.sysdummy1;

Here follows a more complete example with more functions and conversions, that demonstrates selecting and converting data from an actual (though temporary) table:

-- Create a temp table to demonstrate with:
declare global temporary table DateExample (
  ID Integer,
  RealTSField Timestamp,
  DateCharField CHAR(8),
  TSCharField CHAR(20)
  ) with replace;

-- Insert a demo record to work with:
insert into session.DateExample values(1, '2014-01-01 12:30:23.123456', '20140101', '20140101123023123456');

-- Show what's in the table at this point:
select * from session.DateExample; 

-- Now show the conversions in action:
select 
  d.*,
  date(DateCharField||'000000') DateFromDateCharField, -- implicit conversion from YYYYMMDD via tacking on HHMISS as '000000' string
  timestamp(DateCharField||'000000') TSFromDateCharField, -- implicit conversion from YYYYMMDD via tacking on HHMISS as '000000' string
  timestamp(LEFT(TSCharField,14)) TSFromTSCharField, -- implicit conversion does not support fractional seconds hence we must use LEFT()
  timestamp_format(TSCharField, 'YYYYMMDDHH24MISSNNNNNN') TSFromCharField_ExplicitFormat --explicit conversion with timestamp_format
from session.DateExample d;

Probably the easiest way to try this out is by pasting into Ops Navigator "Run SQL Scripts" window, placing the cursor on the first line, then repeatedly pressing Ctrl-Y to successively run each statement to follow along.