Date formate

[#1]

Hi All,

i have a date formate of this type  CD(2014-06-10 00:07:51.63) . I wanted to know how do i get the month and year out of this .

I tried with the following expression but its not working year(CD) as year .

Thanks in Advance

[ecolomer]

Try with subfield function or mid

mid(Date, 1,4) as year

mid(Date, 6,2) as month

[its_anandrjs]

Hi,

You can try some of this ways by subfield also but after this if the date format not match in the field then format date in the load script.

Tab1:
LOAD Datetime,
Date(Date#(Mid(Datetime,4,4),'YYYY'),'YYYY') as Year,
Date(Date#(Mid(Datetime,9,2),'MM'),'MM') as Month,
Date(Date#(Mid(Datetime,12,2),'DD'),'DD') as Day,

SubField( SubField(Datetime,'(',2),'-',1) as Yearchk,
SubField( SubField(Datetime,'(',2),'-',2) as Monthchk,
Right(SubField( SubField(Datetime,'(',2),' ',1),2) as Daychk;
LOAD * INLINE [
Datetime
CD(2014-06-10 00:07:51.63)
];

Regards

Anand

[jagan]

Hi,

Try like this

LOAD

*,

Year(FormattedDate) AS Year,

Month(FormattedDate) AS Month;

LOAD
*,

Date(Date#(Left(CD, 10),  'YYYY-MM-DD')) AS FormattedDate

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

[Anonymous]

AVOID Dates.

Use only Numbers. Previous You must convert The Date to number.

Date(0) is 18991231.

A.Caria

[MarcoWedel]

Hi,

supoosed your field literally contains the text 'CD(2014-06-10 00:07:51.63)' then one solution could be

to extract the month:

=Month(Timestamp#(TextBetween('CD(2014-06-10 00:07:51.63) ','(',')'),'YYY-MM-DD hh:mm:ss.ff'))

to extract the year:

=Year(Timestamp#(TextBetween('CD(2014-06-10 00:07:51.63) ','(',')'),'YYY-MM-DD hh:mm:ss.ff'))

You might as well extract the timestamp in the script and then get the year and month from it with a preceding load like:

LOAD *,
          Month (TimestampField) as MonthField,
          Year(TimestampField) as YearField;
LOAD Timestamp#(TextBetween('CD(2014-06-10 00:07:51.63) ','(',')'),'YYY-MM-DD hh:mm:ss.ff') as TimestampField
FROM yoursource;

hope this helps

regards

Marco

[saumyashah90]

CHECK THIS FILE