How to store Date and time variable value into Schema column with has Data type as Datetime in Classic V8 or V7

[SantoshKu20]

Hi,

I have configured the WebApp for multi selection value in the Page and capturing value into JS and try to store value into Schema columns but some of the Columns data not updating.

Ex: Current Date not inserting into Creation_Date Column.

and also some of other String variable values are not inserting into the Schema columns.

Can anyone suggestion or advise the approch.

I have done below Script to Write into shcema.

// Load Libraries
loadLibrary("/nl/core/shared/nl.js");
NL.require('/nms/campaign.js')

//Set RecipeintID

var rcpID = ctx.recipient.@id;

var queryRC = xtk.queryDef.create (
<queryDef schema="nms:recipient" operation="select">
<select>
<node expr="@ID_PARTENAIRE"/>
<node expr="@id"/>
<node expr="@Entity"/>
<node expr="[person/@Id_External_Upd]" alias="Id_External_Upd"/>
<node expr="[person/@Id_Ceh_Account]" alias="Id_Ceh_Account"/>
<node expr="[person/@Is_Deleted]" alias="Is_Deleted"/>
<node expr="[person/@Total_Business_Before_Transco]" alias="Total_Business_Before_Transco"/>
<node expr="[person/@Datasource]" alias="Datasource"/>
</select>
<where>
<<condition expr={"[@id] = '"+ctx.recipient.@id+"'"}/>
</where>
</queryDef>)

var vRCPquery = queryRC.ExecuteQuery();

var wrIdprtnr = vRCPquery.recipient.ID_PARTENAIRE;
var wrcpId = (String(vRCPquery.recipient.id));

var wrcpbu = vRCPquery.recipient.Entity;
var wrcpIEUpd = vRCPquery.recipient.Id_External_Upd;
var wrcpICA = vRCPquery.recipient.Id_Ceh_Account;
var wrcpIsD = vRCPquery.recipient.Is_Deleted;
var wrcpTBBT = vRCPquery.recipient.Total_Business_Before_Transco;
var wrcpDataS = vRCPquery.recipient.Datasource;
var wrcpFOI = ctx.recipient.Fieldsofinterest.@Name;

var currentDate = formatDate(new Date(), "%2M/%2D/%4Y %1H:%02N:%02S %P");

logInfo("Preference Selected" + ctx.recipient.Fieldsofinterest.@Name)

// Write records into Custom Schema 
xtk.session.Write(<CustomSchema xtkschema="cus:CustomSchema" _operation="insertOrUpdate" _key="@ID_PARTENAIRE,@Name,@Business" ID_PARTENAIRE={wrIdprtnr} Id_Recipient={wrcpId} Business={wrcpbu} Name={wrcpFOI} Id_External_Upd={wrcpIEUpd}
Id_Ceh_Account={wrcpICA} Is_Deleted={wrcpIsD} Total_Business_Before_Transco={wrcpTBBT} Datasource={wrcpDataS} Create_Date={currentDate}/>);

[Jyoti_Yadav · Accepted solution]

Hi @SantoshKu20 ,

You are getting error because of wrong Date format. Your recipient table stores date in a different format than what you have specified in the currentDate.

Try using the syntax:

var currentDate = formatDate(new Date(), "%4Y%2M/%2D %02H:%02N:%02S");

If you are still getting error, please share screenshot of one value stored in the table, to understand the table format your DB saves and a clear screenshot of error it says.

Thanks,

Jyoti

[SantoshKu20]

Hi @Jyoti_Yadav,

Data variable working with your variable, but one / is missing.

So, final one is var currentDate = formatDate(new Date(), "%4Y/%2M/%2D %02H:%02N:%02S");

And I need one more help on same script,

Unable to insert values into Key Columns of my custom Schema. This Schema has linked to Recipient Schema.

Sharing Script for only Key columns insertion, can you please help on this.

// Load Libraries
loadLibrary("/nl/core/shared/nl.js");
NL.require('/nms/campaign.js')

//Set RecipeintID

var rcpID = ctx.recipient.@id;

var queryRC = xtk.queryDef.create (
<queryDef schema="nms:recipient" operation="select">
<select>
<node expr="@id"/>
<node expr="@ID_PARTENAIRE"/>
<node expr="@Entity"/>
</select>
<where>
<condition boolOperator="AND" expr={"@id = "+ rcpID}/>
<condition expr={" @Entity = 'Retail'"}/>
</where>
</queryDef>)
var vRCPquery = queryRC.ExecuteQuery();

var ID_PARTENAIRE = (String(vRCPquery.recipient.ID_PARTENAIRE)).toLowerCase();

var Business = (String(vRCPquery.recipient.Entity)).toLowerCase();

// Write records into Custom schema
xtk.session.Write(<Custom xtkschema="tms:customSchema" _operation="insertOrUpdate" _key="@ID_PARTENAIRE,@Name,@Business" ID_PARTENAIRE={ID_PARTENAIRE} Business={Business}  />);

Thank you.