I have a problem I’ve just found the solution to that I feel could be handled differently in Qualtrics, and wanted to post here for comments and awareness.

The Scenario:

I have a Data Project containing a dated column. My goal is to export the dates as UTC timestamps using ISO 8601 format so it can be consumed by a downstream application.

• The dates were loaded in as ISO 8601 originally.
• The Data Project was correctly configured so the column is set as date, rather than text type. 

Export Workflows

Basic Export

In this case I’m just l have a workflow which:

1. Loads data from the data project.
2. Loads via SFTP to the destination

Outcome:
Data is exported, but the date is formatted yyyy-MM-dd HHss (2024-05-24 00:00:00). 

Export with Basic Transform

Scenario 2 is the same as 1, with an additional step to format the date:

1. Load data from the project
2. Basic transform to transform the dates
3. Load to SFTP

For the basic transform I do the following:

1. Select the data source (the import)
2. Rename the output columns for the dates (they’re all appended (GMT+1) so the original name is preserved
3. Change the format (default is text) to date.
4. Add the date format

This is where things start to go wrong, as I must specify an input and an output format. I know I want ISO 8601 as the output, but I’m not sure of the input, since it’s the Data Project’s native format. I tried the following (all of these failed), and the output format was unchanged.

• ISO 8601 - Playing around in the Basic Transform settings shows ISO 8601 is the ‘default’, so I tried both input and output as the same
• The original input format. This was also ISO 8601, but with an offset instead of a ‘Z’
• The other canned options from the dropdown

The Solution

Luckily, the workflows have a report to show what went wrong during the transform. This reveals, perhaps unsurprisingly, the format it’s trying to match is the format the output happens in - yyyy-MM-dd HHss (2024-05-24 00:00:00). When I added this as a custom input format, it works perfectly. Problem solved.

Conclusion

OK, so I’m able to export the data I want, but why was it such hard work to perform such a basic task? Since the source of the data is a Data Project, I’d expect the ‘input format’ to be listed as something like ‘Database Native’. Perhaps it shouldn’t even be an editable field, as I have no control over the Data Project date format. I’d welcome any comments from Qualtrics or the community.