Hi,

I am trying to insert some values from Webfocus into DB2 table.

I am using the following code. &NOW is giving out he timestamp in correct format that is expaected.


-SET &NOW = HCNVRT(HGETC(8,'HYYMDm'),'(HYYMDS)',30,'A30');

ENGINE DB2 SET DEFAULT_CONNECTION DATABASE
SQL DB2
INSERT INTO SCHEMA.TABLENAME
(APPLICATION_NAME,
REPORT_RUN_TIME,
FLAG)
VALUES(
'XXX',
'&NOW',
'Y');
END
-RUN

When I try to run the above query, I am getting the following error.

(FOC1400) SQLCODE IS -180 (HEX: FFFFFF4C)
: [22007] [IBM][CLI Driver][DB2/AIX64] SQL0180N The syntax of the string
: representation of a datetime value is incorrect. SQLSTATE=22007
(FOC1414) EXECUTE IMMEDIATE ERROR.


Regards,
Ramya

This message has been edited. Last edited by: Ramya,

quote:

timestamp in correct

Looks like DB column is in Date time format. Can you share the DB format for column - REPORT_RUN_TIME.

But NOW variable is in Alpha format. Use .TYPE to verify format of amper variables.

-SET &NOW = HCNVRT(HGETC(8,'HYYMDm'),'(HYYMDS)',30,'A30');

-TYPE FORMAT OF VARIABLE NOW: &NOW &NOW.TYPE

Thanks,
Ram

quote:

share the DB format for column

Hi Ram,

You are right. The DB format of the column 'REPORT_RUN_TIME is 'TIMESTAMP' and the date we are trying to insert is 'Alpha'.

I want your help in converting the type from 'Alpha' to 'DateTime'.

Regards.
Ramya

You need to change the alpha date format to timestamp format while inserting the values. Below code should work. Also this is related to DB2 nothing to do with WebFOCUS.

INSERT INTO SCHEMA.TABLENAME (APPLICATION_NAME,REPORT_RUN_TIME,FLAG)
VALUES ('XXX', TIMESTAMP_FORMAT('&NOW.EVAL','YYYY/MM/DD HH24:MI:SS') ,'Y')

Thanks,
Ram

quote:

TIMESTAMP_FORMAT('&NOW.EVAL','YYYY/MM/DD HH24:MI:SS')

Thanks Ram,

That worked.

Regards,
Ramya

Good to see it solved your problem.
Mark subject line of your first post to SOLVED.