Date Format Issue

[#1]

Hi,

I have a date field which has values in two formats. The values prior to 2014 are in the format 'MMM/DD/YYYY' whereas in 2014 the format 'DD-MMM-YYYY'.

I want to derive Year value out of this date field. How do i do that?

Thanks,

Asma

[alexandros17]

if both formats are recognised as date field simply use year(myField)

let me know

[#3]

Both of them are not recognised as date fields. For the dates in the format 'DD-MMM-YYYY', (i.e. only 2014 dates), we are getting correct values for year(myField), but for the dates in the format 'MMM/DD/YYYY', year(myField) returns null

[alexandros17]

try to format dates, with date#(myField,'DD-MMM-YYYY')

[jagan]

Hi,

Try like this using Alt()

LOAD

*,

Date(Alt(Date#(DateFieldName, 'MMM/DD/YYYY'), Date#(DateFieldName, 'DD-MMM-YYYY'))) AS NewDateField

FROM DataSource;

Note: Replace DateFieldName with your actual field name.

Hope this helps you.

Regards,

Jagan.

[Anonymous]

Hey,

User year(Date(myFiled)) should recognize.

BR,

chinna

[MK_QSL]

Load

  DateField,

  Month(DateField) as Month,

  Year(DateField) as Year;

Load

  Date(Alt(Date#(DateField,'MMM/DD/YYYY'), Date#(DateField,'DD-MMM-YYYY'))) as DateField

Inline

[

  DateField

  Jan/01/2012

  Mar/05/2013

  25-Nov-2014

];

[#8]

Thanks Jagan...

But after using the alt() function, the NewDateField which is created only contains those values which had format as MMM/DD/YYYY. They are converted into DD-MMM-YYYY format which is correct. But those dates which were already in the format DD-MMM-YYYY do not appear in the NewDateField.

[Anonymous · Accepted solution]

Hi

Try this

=year(IF(ISTEXT([datefieldname])=0,Date(NUM([datefieldname])),date(date#([datefieldname],'MMM/DD/YYYY'),'MMM/DD/YYYY')))

Thanks,

Nisha loganathan

[#10]

Thanks a lot Nisha!!!