Date format issue while transforming data

[AbdulRahim Shai]

Hello Developers,

Hope everyone is doing great!

While loading data using import sets/ transform map date format is getting changed.

The source instance date  format is: MM-dd-YY HH:MM:SS

The target instance  date  format is: YY-MM-dd HH:MM:SS

While loading data in the staging table in target MM-dd values are getting swapped.

for example : 

Created field in source: 06-11-2020

Created field in stating table after loading data changes to 2020-11-06 (MM got swapped with DD)

Requesting your kind assistance to help me solve this problem.

I am debugging this and can't figure out a solution.

Thanks,

AbdulRahim Shaikh

[AbdulRahim Shai]

Hey Shubham,

The problem is values are flipping while loading data into the staging table itself not after transforming.
I need to prevent that.

Requesting you to help on this.

THanks

[Kelly3]

So had similar issue. Had to recreate the field I was using in the staging table, as a "string" and NOT a date field.

See KB https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0955636

Then on the Transform map set the date formatting to "YYYY-MM-dd" and also set the date formatting on the user running the import on there user profile to "YYYY-MM-dd" see https://docs.servicenow.com/en-US/bundle/utah-platform-administration/page/administer/field-administ... 

Field

Select the field to transform. The list presented contains only those field types (integer and string) from the table selected that can be transformed. Note: The sys_user record that initiates the transform process must have its date format set to the default format of "yyyy-MM-dd." Any other date format causes an error during transformation. This problem is only specific to transforming TO TARGET fields of type Date/Time. This problem is not an issue if the target field type is of type String or if the field mapping for the date field is changed to the same date format as the transformation process.

[Kelly3]

Steps to resolve:

1. Delete the date or date time field from your staging table

2. Recreate the field as a string

3.Set the "Date Format" on the Field Map in the Transform map for the date field to "yyyy-MM-dd hh:mm:ss"

You could also add Use source script = true

Add script:

var gdt = new GlideDateTime(source.<source_field_name>);

return gdt.getValue();

4. On the user that is used for the import change there default date settings "Date format" "

yyyy-MM-dd"