topic Re: Date Conversion in Connectivity & Data Prep

Date Conversion

nicouek — Wed, 06 Sep 2023 19:23:40 GMT

Hello all,

I recently appended a REST API source data to an Excel Flat file for an incremental load setup, however it seems the date format in the REST API source is different from the format present in the Excel file. Qlik Sense output is as seen below:

I need help converting both dates into one standard date.

Please help... Thank you

Re: Date Conversion

BrunPierre — Wed, 06 Sep 2023 21:44:36 GMT

Try

=Date(Alt(Floor(Date), Date(Floor(TimeStamp#(Date, 'MM-DD-YYYY hh:mm:ss')))), 'DD/MM/YYYY')

Re: Date Conversion

Vegar — Thu, 07 Sep 2023 04:54:21 GMT

You should consider handling this in the script. Take a look at my script samples below, they might help you.

Set TimestampFormat='MM-DD-YYYY hh:mm:ss';

Set DateFormat ='DD/MM/YYYY';

LOAD dayname(datefield) as DATE, ...

Or as @BrunPierre suggest, like this.

LOAD

Date(Alt(Floor(datefield), Floor(TimeStamp#(datefield, 'MM-DD-YYYY hh:mm:ss'))), 'DD/MM/YYYY') as DATE, ...