Mirth Connect SQL Server Error : Conversion failed when converting date and/or time from character string

Question

I am trying to insert into SQL Server DateTime field. Trying simple scenario of one table having datetime column named start_date only.

Query I am trying is

INSERT INTO test (start_date) values (${start_date})

start_date is channelMap variable of Type java.util.Date , It was created using :

var start_date = DateUtil.getDate('yyyyMMddHHmmss', msg['date'].toString());

Here start_date is of java.util.Date, why mirth treats it as String when it tries to insert into database ??

Answer 1

You can handle the conversion even in SQL. Hope it helps

var start_date = msg['PID']['PID.7']['PID.7.1'].toString(); // 19831123 - YYYYMMDD format
try {
  sql="INSERT INTO test (start_date) values (convert(datetime,'" + start_date + "',5))";
  logger.info(sql);
  rst = dbConn.executeUpdate(sql);  
}
catch(err) {
  logger.info('ERR: ' + err);
}

Out in DB will be below.

select * from test
start_date |
----------
1983-11-23 00:00:00.000

2nd Approach

If you still want to use util try below

var start_date = msg['PID']['PID.7']['PID.7.1'].toString(); // 19831123 - YYYYMMDD format
/* 
Input is yyyyMMdd and 
output is yyyyMMddHHmmss format
*/
var datestring = DateUtil.convertDate('yyyyMMdd', 'yyyyMMddHHmmss', start_date); 
try {
  sql="INSERT INTO test (start_date) values ('" + start_date + "')";
  logger.info(sql);
  rst = dbConn.executeUpdate(sql);  
}
catch(err) {
  logger.info('ERR: ' + err);
}