Formatting the date in workflows | Experience Community

matt.fowles · 2024-05-24T15:05:48+00:00

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 WorkflowsBasic ExportIn this case I’m just l have a workflow which:Loads data from the data project. Loads via SFTP to the destinationOutcome:Data is exported, but the date is formatted yyyy-MM-dd HH:mm:ss (2024-05-24 00:00:00). Export with Basic TransformScenario 2 is the same as 1, with an additional step to format the date:Load data from the project Basic transform to transform the dates Load to SFTPFor the basic transform I do the following:Select the data source (the import) Rename the output columns for the dates (they’re all appended (GMT+1) so the original name is preserved Change the format (default is text) to date. Add the date formatThis 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 dropdownThe SolutionLuckily, 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 HH:mm:ss (2024-05-24 00:00:00). When I added this as a custom input format, it works perfectly. Problem solved.ConclusionOK, 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.

Sign up

Login to the Community

Scanning file for viruses.

This file cannot be downloaded