Extract Year

[engishfaque]

Dear All,

Please help me to extract date, month and year from the listed below data,

I have two formats of data/time in my sales file, where from i have to extract Date, Month, and Year. Each values should be separate as I can assign to variables.

Mar 9, 2015 12:00:40 AM PDT

or

Mar 9, 2015 12:00:40 AM PST

Example file is also attached, please find.

Kind regards,

Ishfaque Ahmed

[engishfaque]

Any update?

[anbu1984]

LOAD Year(Date#(Left([date/time],23),'MMM DD, YYYY hh:mm:ss TT'))

FROM

[TestFile.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

[jyothish8807]

Hi Ishfaque,

Try like this:

Mar 9, 2015 12:00:40 AM PDT here Replace F1 with your field.

Load

left(F1,3) as Month,

replace(subfield(F1,' ',2),',','') as Date,

mid(F1,7,5) as Year

resident Tab1;

Regards

KC

[mukesh24]

Hi,

first convert u r data into date format by using date() or date#() then apply Year() ,month() to generate date.

Regards,

Mukesh

[robert_mika]

Directory;

LOAD left([date/time],3) as Month,

  TextBetween([date/time],' ',',') as Day,

  TextBetween([date/time],' ',' ',2) as Year

FROM

TestFile.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

[robert_mika]

Jyothish

Your expression for Year is not dynamic.

It will fail on

Apr 25, 2015 12:18:54 AM PDT

or row where the Day has two characters.

[jyothish8807]

Right my bad. Thanks for highlighting it.

Regards

KC

[#9]

Hi Ishfaque,

   Try this type of code in scripting and then you will get first  "Mar 9,2015" after that you can get separately day,month and year.

Dates:

LOAD

     @1,

     Left(FileName(),12)     AS    Dates

FROM

[Comunity\Mar 9, 2015 120040 AM PDT.txt]

(txt, codepage is 1252, no labels, delimiter is '\t', msq);