Seconds field is not updating for datetime field in Database.

Question

I have a datetime field as "2018-11-08T11:23:34+0000". I want to add this to database in a datetime type column. I'm using totime("11:23:34") but the 'seconds' field always showing '00'. How do I update 'seconds' field in database?

hiennguyen · Answer

Hi if you just try to write the datetime string to a datetime column in DB, you can do something like separate the date and the time from the input string, then do todate([date])+totime([time]). Now it becomes a datetime in Appian and you can assign it to a CDT field and write to DB like normal (if you just use todatetime([entire string]) Appian will set "seconds" value to 0) 
 Hope this help

Vincent Mark · Answer

Appian datetime and MySQL datetime formats are different so I suggest writing an expression rule to cast Appian datetime to MySQL's "YYYY-MM-DD HH:MM:SS" format. You might have to modify the provided code to suit your needs. 
 
 load(
 local!dt: now(),
 local!AppianDateTimeString: tostring(
 local!dt
 ),
 /* MySQL retrieves and displays DATETIME
 values in 'YYYY-MM-DD HH:MM:SS' format */
 local!MySQLDateTimeString: joinarray(
 {
 /* Date string */
 joinarray(
 {
 year(local!dt),
 month(local!dt),
 day(local!dt)
 },
 /* forward slash */
 char(45)
 ),
 /* Time string */
 joinarray(
 {
 hour(local!dt),
 minute(local!dt),
 second(local!dt)
 },
 /* colon */
 char(58)
 )
 },
 /* space */
 char(32)
 ),
 {
 /* Appian Datetime String */
 local!AppianDateTimeString,
 /* MySQL Datetime String */
 local!MySQLDateTimeString
 }
)