Date format issues in load script

[Anonymous]

Hi

I have a spreadsheet with the date data as 3/16/2015 (to be read M/DD/YYYY)

How can I format this in the load script to load in as DD/MM/YYYY so each month will be a double digit number?

[Anonymous]

change the script:

Date( YourTimeDimension,  'DD/MM/YYYY') as YourTimeDimension,

[Gysbert_Wassenaar]

Excel dates are often already numeric values with date formatting. In that case you only need the date() function:

LOAD

     date(MyExcelDate, 'DD/MM/YYYY') as MyNewDate

FROM ....

But if the excel values are string values then you also need the date#() function:

LOAD

     date(date#(MyExcelDate, 'M/DD/YYYY'),'DD/MM/YYYY') as MyNewDate

FROM ....

[MK_QSL]

Date(Date#(DateField,'M/DD/YYYY'),'DD/MM/YYYY')  as DateField

[avinashelite]

Refer to this document for the date formatting

[sunny_talwar · Accepted solution]

gwassenaar does the order of Date#(Date(...)) matter? I feel that shouldn't the second MyNewDate be like this?

LOAD

    Date(Date#(MyExcelDate, 'M/DD/YYYY'),'DD/MM/YYYY') as MyNewDate

FROM ....

Let me know if it doesn't matter.

Best,

Sunny

[Anonymous]

Many thanks!! Date(Date#.......worked like a charm!!

[Gysbert_Wassenaar]

Yes, you're totally right. Thanks for pointing out my error.

[sunny_talwar]

That was more sort of a question than to point out your error. But I am glad it served both the purposes

Best,

Sunny