Converting multiple date formats

[#1]

I have a region and date field loaded in as below :

Region,

date#(Date,'MM/DD/YYYY') as SALES _DATE

However where Region = Europe my date formats are in DD/MM/YYYY. For instance in my raw data  I have dates such as 1/4/2013, 1/9/2013 which really should read 4/1/2013, 9/1/2013. How do I load these specific dates ie where region = america differently ?

[maxgro]

you can change format depending on Region

Region,

if(Region='............',  date#(Date,'MM/DD/YYYY'),

if(Region='............',  date#(Date,'DD/MM/YYYY'),

                                   date#('DD-MM-YYYY')    // default format

)) as SALES _DATE

[#3]

Thanks maxgro

This is what I have but I realised it's now only showing the first date value for each month which is really odd :

if(Region='Americas', date#(Date,'DD/MM/YYYY'),date#('MM/DD/YYYY')) as SALES_DATE,

[swuehl]

Shouldn't you use

if(Region='Americas', date#(Date,'MM/DD/YYYY'),date#('DD/MM/YYYY')) as SALES_DATE,

or

if(Region='Europe', date#(Date,'DD/MM/YYYY'),date#('MM/DD/YYYY')) as SALES_DATE,

?

A wrong date format might explain that some dates couldn't be interpreted, but it wouldn't explain that you only see first date for each month.

[#5]

Thanks swuehl but I can't see how that's any different from my original code.

Just to confirm it's only the 'Americas' region which is currently in DD/MM/YYYY. The rest of the regions would be loaded as normal.

[swuehl]

My code differs to your code posted in your second post in the format codes used.

Just to confirm it's only the 'Americas' region which is currently in DD/MM/YYYY. The rest of the regions would be loaded as normal.

I am little confused. In your original post, you said:

...However where Region = Europe my date formats are in DD/MM/YYYY

Not sure if I understand your issue, could you post some sample lines of data?

[#7]

Sorry let me clarify.

I have a number of regions aside from Americas where the date format is already in MM/DD/YYYY.

Only for Americas are the dates in DD/MM/YYYY and these need to be converted.

Regard to only seeing one date value for each month - I've worked out what's happening. Basically the script is 'only' loading the 'Americas' dates whereas it should be loading all dates and converting the format where necessary.

Hope that clarifies.

[swuehl · Accepted solution]

I think there is the field Date missing in second date#() function, I assume this is only a copy & paste error in the post?

if(Region='Americas', date#(Date,'DD/MM/YYYY'),date#(Date, 'MM/DD/YYYY')) as SALES_DATE,

[#9]

I literally just spotted that ! Good call ... thanks