Does anybody have an example of moving a date type field into the database as date? I have a string coming in from the map and won't go in without conversion/masking. Thanks
You will need to convert to a date format accepted by your database in a Map using the Date Format Function
Thanks Darrell. So, I am inserting into the Oracle DB with a DATE column.
I am working with a string like "01/01/2000". I get an error if I just try to insert it as is. When I channel the string through a Date Format function of the Map shape, what should my Input Mask and Output Mask be? I tried a few things, but couldn't get it to work.
Have you tried setting the element type to date/time both source and destination profile?
This may be helpful. Date/Time Formatting
I don't know what format Oracle expects
It's char on the source side of the map, as I am reading a CSV via SFTP process. It's date on the target side of the map, as it is going into the Oracle DB's DATE column.
Try setting the element type to date/time on the source map
For date formatting the 2 digit month Format mask is MM not mm.
I have looked at my CSV file more closely. Here is what it looks like:
Resource Week,Member Type Name,Member Name
"Jul 3, 2016 12:00:00 AM",Analyst,John Doe
So, the first column is the one I am wrestling with.
What I did is a UDF with two steps:
Step 1 - Scripting:var mystr = input;var output = mystr.replace(/\"/g, "");
Step 2 - Date Format:Input Mask: MMM d, yyyy hh:mm:ss aOutput Mask: yyyyMMdd HHmmss.SSS
Objective still remains: output of the UDF is mapped to a profile field of date/time format that will feed into an Oracle DB column with DATE datatype.
java.sql.SQLException: Unable to set date/time, value 6/26/16 does not match format yyyyMMdd HHmmss.SSS at com.boomi.connector.database.types.TimestampType.setValue(TimestampType.java:51) at com.boomi.connector.database.types.ValueType.applyValue(ValueType.java:37)
You can still use the date format function:
Is there a difference between your proposed function and the one I am using? Your masks appear to be the same as mine and it's not working.
I have put your date in a flat file profile defined like this:
And the input message looks like this:
Then I just map the flat file to it self:
Result after mapping:
I got it to work.
Retrieving data ...