How can I insert a date with Mirth to an Informix DB table?

[alvarodb]

Hi.
When I try to insert a date in a Informix DB table with the destination:

INSERT IGNORE INTO table1(date1) VALUES(DATE(${cod_date}));

and the transformer:

var date_aux1=hl7_xml['ORU_R01.PATIENT_RESULT']['ORU_R01.ORDER_OBSERVATION']['OBR']['OBR.7']['TS.1'];
var date_aux2;
date_aux2=date_aux1.substring(6,8)+'/'+date_aux1.substring(4,6)+'/'+date_aux1.substring(0,4);
localMap.put('cod_date',date_aux2);

the mirth doesn´t work (there is a syntax error).

How can I insert a date with Mirth to an Informix DB table?

[chrisl]

Re: How can I insert a date with Mirth to an Informix DB table?

What is the syntax error that you get?

Try:

var date_aux1=hl7_xml['ORU_R01.PATIENT_RESULT']['ORU_R01.ORDER_OBSERVATION']['OBR']['OBR.7']['TS.1'].text()[0];

-Chris

[alvarodb]

Re: How can I insert a date with Mirth to an Informix DB table?

It´s very easy.

You have only to put the date in the yyyy-mm-dd JDBC format without quotation marks (the date isn't a string) and without using the DATE function.

[toledoj]

Re: How can I insert a date with Mirth to an Informix DB table?

Is there a way to get the date back as a string? In order to make dates more portable across databases I've stored dates as 8 or 18 character strings depending on whether or not Time is needed as well. I've tried using ${Date} with the format strings but it always generates an error.

[chrisl]

Re: How can I insert a date with Mirth to an Informix DB table?

Using ${Date} isn't supported in 1.1, however it will work in 1.2.