datetime2 is unnecessary because it has a higher precision on the milliseconds which is cut-off by the platform anyway.
The returned datetime does not contain an explicit time-zone (no Z, no offset).
The database profile gets the returned datetime without any time-shifting.
Because of the missing time-zone
d.Profile value = SQL response.
By definition the datetime value is UTC!
The database profile always uses Date/Time data type to ensure a normalized data exchange with the SQL script, not relying on any character formating.
Map UTC-Z to a database profile
import com.boomi.execution.ExecutionUtil
import java.text.SimpleDateFormat
final String SCRIPT_NAME = "msgUTC_Z_TO_AtomDT"
inal _logger = ExecutionUtil.getBaseLogger()
_logger.finest('>>> Script start ' + SCRIPT_NAME)
/* **************************************************************************
A Map Script that converts up to three Z formatted UTC dates to ATOM
default format without chaning the time-tone.
IN: inDateTimeZ1..Z3 ISO formatter UTC datetime
Normally coming from a JSON profile with a datetime field
formatted as "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'".
OUT: DateTime1..3 (output is always character)
The UTC datetime formatted in the ATOM default format.
Formatting only, not time-zone shift.
Note: Depending on format of the Inputs:
Character or Date/Time we get a different format in the logs,
with inbound Map from JSON profile:
"CreatedUTC": "2023-07-28T13:20:51.525Z"
> inDateTimeZ1 (Character) : 20230728 142051.525
> inDateTimeZ1 (Date/Time) : Fri Jul 28 14:20:51 BST 2023
--------------------------------------------
2022-08-02 msc - Created
************************************************************************** */
_logger.info('IN_1: ' + inDateTimeZ1 )
_logger.info('IN_2: ' + inDateTimeZ2 )
_logger.info('IN_3: ' + inDateTimeZ3 )
// *******************************************************
// The incoming DateTime is always ATOM local time-zone!
// *******************************************************
// Target format for conversion
final SimpleDateFormat targetTimeZone = new SimpleDateFormat("yyyyMMdd HHmmss.SSS")
// Target time-zone for conversion
targetTimeZone.setTimeZone(TimeZone.getTimeZone("UTC"))
if( inDateTimeZ1 != null && inDateTimeZ1 != "")
{
DateTime1 = targetTimeZone.format( inDateTimeZ1)
}
if( inDateTimeZ2 != null && inDateTimeZ2 != "")
{
DateTime2 = targetTimeZone.format( inDateTimeZ2)
}
if( inDateTimeZ3 != null && inDateTimeZ3 != "")
{
DateTime3 = targetTimeZone.format( inDateTimeZ3)
}
_logger.info('OUT_1: ' + DateTime1 )
_logger.info('OUT_2: ' + DateTime2 )
_logger.info('OUT_3: ' + DateTime3 )
Map UTC-Z to a database profile
/// *************** COPY AND PASTE FROM HERE *****************
import com.boomi.execution.ExecutionUtil
import java.text.SimpleDateFormat
final String SCRIPT_NAME = "msgAtomDT_TO_UTC_Z"
inal _logger = ExecutionUtil.getBaseLogger()
_logger.finest('>>> Script start ' + SCRIPT_NAME)
/* **************************************************************************
A Map Script that converts up to three Date/Time values
into a Z formatted UTC date.
IN: inDateTime1 Date/Time
inDateTime2 Date/Time
inDateTime3 Date/Time
Any "plain" datetime from the platform, so called:
ATOM-DateTime.
Format: *yyyMMdd HHmmss.SSS*
Value : ATOM local time-zone
OUT: DateTimeZ1..3 (output is always character)
The datetime in ISO format ("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"),
containing the same value as the incoming datetime.
Formatting only, not time-zone shift.
--------------------------------------------
2022-08-02 msc - Created
************************************************************************** */
/*
_logger = ExecutionUtil.getBaseLogger()
_logger.info('IN_1: ' + inDateTime1 ) // IN_1: Fri Jul 28 14:20:51 BST 2023
_logger.info('IN_2: ' + inDateTime2 ) // IN_2: Fri Jul 28 14:20:51 BST 2023
_logger.info('IN_3: ' + inDateTime3 ) //IN_3: null
*/
// For performance reasons we allow three conversions in a row.
final SimpleDateFormat utcISO = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")
// The incoming DateTime is always ATOM local tome-zone.
if( inDateTime1 != null && inDateTime1 != "")
{
Date dt1 = inDateTime1
DateTimeZ1 = utcISO.format( dt1)
}
if( inDateTime2 != null && inDateTime2 != "")
{
Date dt2 = inDateTime2
DateTimeZ2 = utcISO.format( dt2)
}
if( inDateTime3 != null && inDateTime3 != "")
{
Date dt3 = inDateTime3
DateTimeZ3 = utcISO.format( dt3)
}
Observations
d.INC.DynUpdate: 20230728 142051.525
d.Get Incident:
SQL Return DataType
DB Connector DataType
Boomi Response: DB Profile
Map to j.Profile
DateTime2
Character
2023-07-28 14:20:51.5250000
DateTime2
Date/Time
20230728 142051.525
2023-07-28T13:20:51.525Z
datetimeoffset(3)
Character
2023-07-28 14:20:51.525 +00:00
datetimeoffset(3)
Date/Time
20230728 152051.525
2023-07-28T14:20:51.525Z
Boomi Response Comments
1400 is returned - this is 1:1 to what was written, however in the format specified by the DB (ā character type field)
The datetime 1400 that was written to the DB is returned in the well defined Boomi Internal Format. This is what we want!
The datetime is returned with an offset +00 so that the platform could interpret this as UTC. However, because of datatype Character no transformation takes place and we see the DB returned datetime.
The datetime is (internally) returned to the DB connector with an offset +00 so that the platform does interpret this as UTC. The target database profile field type is set to Date/Time, and the platform shifts the returned UTC date (1400) to the ATOM time-zone (+1): 1500 on the profile.
Map to j.Profile comments
In any case, the DB profile DateTime is interpreted as ATOM local and a time-shift will take place.
Surprising to notice that the shift will also take place when using the Date Format function in a mapping.
š” Mapping **Date Format** does time-zone shifting, too!
There is actually no difference between a direct assignment from d.Profile Date/Type to j:Profile DateTime of āZā type and a date format shape in between.
š” Date Format shapes between datetime fields are unnecessary!
Mappings
If the target profile specifies a DateTime field of format yyyy-MM-dd'T'HH:mm:ss.SSS'Z' you cannot send a value of 2023-07-28T14:20:51Z to it (DateTimeZ Script output): Error Formatting Date '2023-07-28T14:20:51Z', Expected Format yyyyMMdd HHmmss.SSS: Unparseable date: "2023-07-28T14:20:51Z"
Database profile datetime data type Character
Cannot use yyyyMMdd HHmmss.SSS to send it to the DB!
declare @dt as datetime = ? -- '20230728 142051.525'
[22007][241] Conversion failed when converting date and/or time
from character string.
SQL Server requires ISO, in case characters are sent.
