Need help in converting Distorted Date

[#1]

Hi all,

I have a Date like below, How could I convert into MM-DD-YYYY.

     

~

Walter

[sunny_talwar]

Images are not visible, would you be able to reattach?

The basic idea is to do something like this:

Date(Date#(DateField, 'YourCurrentFormatHere'), 'MM-DD-YYYY') as DateField

[swuehl · Accepted solution]

If your date field shows different formats (and this is what you called distorted), you can look into Alt() function to test different date formats:

Example from HELP:

alt(

date#( dat , 'YYYY/MM/DD' ),

date#( dat , 'MM/DD/YYYY' ),

date#( dat , 'MM/DD/YY' ),

'No valid date'

)

In general, have a look into

Why don’t my dates work?

[#4]

There is some more data which is not shown in the image. Where you could find data as

2914

2421

Where i assumed it to be Feb 9th 2014 and Feb 4th 2021 but its not.

Thank you Swuehl.

[#5]

Your attachment in the original post did not work so I am just going to explain based on an example.

Before your load script.. Set the Date and Time formate as below :

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff] TT';

And try below in your load script.

Date([Your Date Field])     AS     DateField

It works for me. It turns our client's Australian dates (DD/MM/YYYY) into American (MM/DD/YYYY)

[swuehl]

And how do you interpret

12212

January 22 2012 or December 2nd 2012?