Hi All,
I am using read excel function to read the excel file and mapping these values to CDT.
I am giving timestamp field in excel and trying to map with CDT having data type 'Date and Time', and in postgres Db it has timestamp field, but it is not getting mapped to CDT.
NOTE: If I enter only Date then it is getting mapped to CDT and updating the DB, but If I am trying with timestamp field the CDT values are null.
Thanks,
Kiran
Discussion posts and replies are publicly visible
Hi Kiran,
Can you also attach a screenshot of the mapping rule/code you are using to task this dict to CDT? To me, it looks like you might need to change the format of the date, or maybe the datatype of the field in CDT is misconfigured.
Hi Harshit,
Format of the date is fine, whenever I add (8/17/2022 10:10:00) then the file is read by rule but the mapping is not happening for any values to CDT
I need to see how you have configured the casting expression rule. Please attach the code or screenshot of it.
Attaching sample code
You have to use toDatetime function to cast it into Date and Time type.
Hi deepak,
I try to cast the values as per CDT but giving this error. I am passing date as MM-DD-YYYY HH:MM:SS AM/PM.
NOTE: I am using postgres DB
you have to modify the date format first and then use todatetime. for that you have to see your local variable data.
I am attaching whole snap of my rule just let me know where do I need changes and which format should I pass in excel
Attaching the snap of my rule
Could you please show me the value of local!recordSet1 of index 1st. Basically i want to see the 5th or 6th column values from the excel read function.
Most probably we have to use the below code:
a!localVariables( local!a: "2004-04-13 14:09:20", datetime( split(split(local!a, " ")[1], "-")[1], split(split(local!a, " ")[1], "-")[2], split(split(local!a, " ")[1], "-")[3], split(split(local!a, " ")[2], ":")[1], split(split(local!a, " ")[2], ":")[2], split(split(local!a, " ")[2], ":")[3] ) )
Local!a will be your datetime column index.