Importing Time / Date data

[sigmachiuta]

I am importing data from Excel.   The problem I am having is errors in the import log...I am looking in Excel and the format of the field is

2010-03-11 10:26:28

And in my transform map I have specified the date format seen below, so i am confused why i am receiving this error .   It looks like the date is correct but it is trying to format 03-11-2010 without time.   This is an excel sheet that i have reformatted to use yyy-MM-dd hh:mm:ss   Is Snow not recognizing the changed format?

com.glide.db.impex.transformer.TransformCoercionException: Unable to format 03-11-2010 using format string yyyy-MM-dd hh:mm:ss for field u_open_time

[PeterWiles]

Ah, data/times on imports.

When you do the first import and create the transform map, the data field on the transform table is a string, not a date/time field.

As long as your string in the excel doc is set as a data time, on the transform map, don't try to map it directly.

On the transform map, use the script option to cast the field as a glide date/time and set the field to this.

So something like:

if(source.u_open_time != "")

{

        var time = new GlideDataTime(source.u_open_time);

      target.open_time = time;

}

This will also get around another quirk where if the time is 12:00 (midday), SN maps it as 00:00 (midnight)

Pete

[sigmachiuta]

Ok thanks i will try this.   Looking at my import set table the time was not brought in.   I am not sure if that is because it doesnt see the excel format but if i copy the date/time field from excel into notepad and then paste it back in to excel the time does show on the import set table.

[jarodm]

This looks good, except you'll want `GlideDateTime` instead of `GlideDataTime`.

[sigmachiuta]

This did not work.   I used the script and changed it per field in the transform map, i just receive blank values but no errors.   It shows correct in the import set table, just not in the destination table.

if(source.u_first_look != "")

{

        var time = new GlideDataTime(source.u_first_look);

      target.u_first_look =   time;

}