Date ISO issue

[Anonymous]

I usually get the month name using MonthName (Date Column) as Month in the script. But this time it is not working since my the date values are in some ISO format. Date vaules in the date column is like as follows. Any suggestions please???

2015-01-05T20:13:58

[robert_mika]

Try:

=monthname(SubField('2015-01-05T20:13:58','T',1))

[Anonymous]

Thanks for replying.

I dont know where to use this.

I have the below fields in my table

ID

Title

Status

Date

Since I dont have a month field. I usually include a field as Month (Date) as Month so that I can use a dimension as Month. This usually works. But this time the date format is different in my file. It is like below. So not working. Please suggest on how to create a filed for month in my script.

My Script:

LOAD ID,

     TrackingID,

     RequestID,

     DateCompleted,

     Month (DateCompleted) as Month

FROM

[..\January 2015 Orchestrator Data.xlsx]

(ooxml, embedded labels);

DateCompleted

2015-01-03T16:55:08

2015-01-03T17:04:40

2015-01-05T16:00:42

2015-01-05T16:01:08

2015-01-05T03:34:46

2015-01-05T16:04:09

2015-01-05T16:14:32

2015-01-05T20:13:58

2015-01-05T20:24:35

2015-01-06T05:14:20

2015-01-06T05:24:27

2015-01-06T07:24:50

2015-01-06T07:26:00

2015-01-06T08:14:03

[MarcoWedel]

LOAD *,

         Month (DateCompleted) as Month;

LOAD ID,

     TrackingID,

     RequestID,

     Date(Date#(PurgeChar(DateCompleted,'T'),'YYYY-MM-DDhh:mm:ss')) as DateCompleted

FROM

[..\January 2015 Orchestrator Data.xlsx]

(ooxml, embedded labels);

[MarcoWedel]

or better

LOAD *,

         Month (DateCompleted) as Month;

LOAD ID,

     TrackingID,

     RequestID,

     Date(Date#(Replace(DateCompleted,'T',' '),'YYYY-MM-DD hh:mm:ss')) as DateCompleted

FROM

[..\January 2015 Orchestrator Data.xlsx]

(ooxml, embedded labels);

if there might be days or hours with only one digit length.

regards

Marco

[robert_mika · Accepted solution]

LOAD ID,

     TrackingID,

     RequestID,

     Monthname(SubField(DateCompleted,'T',1))

     Month (DateCompleted) as Month

FROM

[..\January 2015 Orchestrator Data.xlsx]

(ooxml, embedded labels);

[Anonymous]

@ Robert Mika - Excellent it worked.

Thanks Marco

[robert_mika]

You are welcome