Creating a date out of a string with odd format

[#1]

I've got a date type field in format 'MMMMM dd, YYYY HH:MM' and I need to make something sensible out of it,  that will still sort correctly as months.

E.g. March 8, 2012 09:25  or March 27, 2012 10:47

At this stage when I "extract"  the month, for example, by using left(Date,index(Date,' ')) as Month, I get the month I want,  but it then sorts in an Alphabetic way,  not in month sequence.  I need all the data found in this field, each in their own format.

ANY help would be appreciated.

[Jason_Michaelides]

Try:

Date(Date#(YourDateField,'MMMM dd, YYYY hh:mm'),'MMMM dd, YYYY hh:mm')     AS     Date,

Month(Date#(YourDateField,'MMMM dd, YYYY hh:mm'))     AS     Month,

in your script.

Hope this helps,

Jason

[#3]

Hi Jason,

No luck - this is my code:

LOAD

Date,
          Date(Date#(Date,'MMMM dd, YYYY hh:mm'),'MMMM dd, YYYY hh:mm') AS MyDate,
          Month(Date#(Date,'MMMM dd, YYYY hh:mm')) AS MyMonth,
          left(Date,index(Date,',')) as MonthDay

and this is the result:

[Jason_Michaelides]

Please provide some sample data.

[#5]

Input is out of a CSV file - here's a snippet:

Date;Item;Destination;Type;Rate;Duration;Amount;

"March 31, 2012 21:26";+27999999999;South Africa-Cape Town;Call;0.000;00:04;EUR 0.000;

"March 31, 2012 15:03";+27999999999;South Africa-Cape Town;Call;0.000;00:00;EUR 0.000;

"March 31, 2012 09:00";+27999999999;South Africa-Cape Town;Call;0.000;00:03;EUR 0.000;

"March 31, 2012 08:41";+27999999999;South Africa-Cape Town;Call;0.000;00:05;EUR 0.000;

"March 31, 2012 08:30";+27999999999;South Africa-Cape Town;Call;0.000;10:25;EUR 0.000;

"March 31, 2012 08:25";+27999999999;South Africa-Cape Town;Call;0.000;03:12;EUR 0.000;

"March 30, 2012 19:30";+27999999999;South Africa-Cape Town;Call;0.000;17:48;EUR 0.000;

"March 27, 2012 10:47";+27999999999;South Africa-Cape Town;Call;0.000;00:27;EUR 0.000;

"March 20, 2012 19:35";+27999999999;South Africa - Mobile;Call;0.195;09:52;EUR 2.039;

"March 20, 2012 19:30";+27999999999;South Africa - Mobile;Call;0.195;05:26;EUR 1.259;

"March 20, 2012 19:29";+27999999999;South Africa - Mobile;Call;0.195;00:00;EUR 0.000;

"March 19, 2012 13:25";+27999999999;South Africa-Cape Town;Call;0.000;10:54;EUR 0.000;

I've anonomised the phone numbers.

[Jason_Michaelides · Accepted solution]

Here you go - hope it helps,

Jason

[#7]

Call the dogs off,  the hunt is over!

Thanx Jason - much appreciated.

[Jason_Michaelides]

Pleasure.

[#9]

Hi Jason,

a few days has gone by, and I've since realised that there is another issue with this.  After you replied to my problem,  you sent me a qvw that I then continued working on - however,  when I try to apply your solution in my own (or another new) qvw,  I run into the same problems again - I think the difference is caused by some (by me) unnoticed setting that you and I have different values for.

Can I send you MY qvw,  and input, so we're looking at exactly the same thing?

[Jason_Michaelides]

OK - I'll look at it when I get a moment.