Converting Date from SAP export DD/MM/YYYY to MM/DD/YYYY

[leale1997]

I have data that was exported from SAP.  The date format from SAP is DD/MM/YYYY.

When QlikView loads the date field it converts 12/07/2017 to December 7, 2017.

It should actually be July 12, 2017.

Since the data is related to historical deliveries there shouldn't be any date values greater than today.

However, because of the format conversion issue there are several dates in the future.

[leale1997]

This is the SAP data pasted into Excel.

[its_anandrjs]

While loading data from SAP make this format initial load

SET DateFormat='YYYYMMDD';

SET TimestampFormat='YYYYMMDD h:mm:ss[.fff] TT';

And to your data load extraction >> after that when reading this data make changes according to your desire format.

Because SAP supports date format as 'YYYYMMDD'

[leale1997]

Thanks for the assist Anand,

I added that to the script for this model.  It's still showing dates in the future.

What am I missing?

[its_anandrjs · Accepted solution]

Try to make changes this ways

LOAD Description,

    VOL,

    [Delivery date],

    Date(Date#([Delivery date],'MM/DD/YYYY'),'MM/DD/YYYY') as NewDate,

    Date(Date#([Delivery date],'DD/MM/YYYY'),'MMMM DD YYYY') as ConvertDate

FROM

[Date Format Issue.xlsx]

(ooxml, embedded labels, table is Sheet1);

Output:-

See attached also

[aarkay29]

Try this

Date#(([Delivery date]),'DD/MM/YYYY') as [Delivery date]

[leale1997]

Super Genius!

Thanks!

[leale1997]

I have one more question?

In this example, how can I format the 'newdate' to read '07/12/2017'?

[its_anandrjs]

Then add one more line in load script

Date(Date#([Delivery date],'MM/DD/YYYY'),'MM/DD/YYYY') as NewChange

[its_anandrjs]

Hi,

First create the QVDs from the base table that is (from SAP with actual date format in the SAP what ever format that you have) and then convert it into your actual format.

Regards

Anand