Date Formats

[danielnevitt]

Hi,

I would really appreciate some help with standardising date formats from two different data sources, as part of my load script.

                         Format

Field1               dd/mm/yyyy hh:mm:ss

Field2               yyyymm

I would ideally like both formats to be changed to mmm-yy, but I am not sure how to write this as part of the load script.

Thanks in advance for any help.

Regards,

Daniel

[Anonymous]

date ( Field1 , 'MMM-YY' )

[its_anandrjs · Accepted solution]

Try to write like

Load

Date(Field1, 'MMM-YY') as  Field1

Date(Field2, 'MMM-YY') as  Field2

From Location;

Or

Load

Date(Date#(Field1, 'dd/mm/yyyy hh:mm:ss'),'MMM-YY') as  Field1

Date(Date#(Field2, 'yyyyymm'), 'MMM-YY') as  Field2

From Location;

[senpradip007]

For Field1

Date(Field, 'MMM-YY')

For Field2

Date(Date#(Field2, 'YYYYMM'), 'MMM-YY')

[tresesco]

Load

     Date(Floor(Field1), 'MMM-YY') as Field1

     Date(Date#(Field2, 'YYYYMM') 'MMM-YY') as Field2

From <>;

Assuming Field1 is being read as a date field already, otherwise parsing function date#() has to be used there too.

[Anonymous]

use the 'replace' function
ex:

u r date is like 20-03-2014

you want show like 20/03/2014

Date(replace(filedname,'-','/'),'DD/MM/YYYY')

case2:

Directly

Date(filedname,'DD/MM/YYYY') use this code for all the dates in the scripy

[danielnevitt]

Hi,

Thank you for all the replies.

I used the function Date(Date#(Field2, 'yyyyymm'), 'MMM-YY') as  Field2

Regards,

Daniel