Date conversion

[AH]

Hi,

I had a date field with values like this: '01-02-2016' and i want to make the value a format like this: '41640'.

How to convert the date value to a number like this?

Any help!

Thanks,

ahmed100

[MarcoWedel · Accepted solution]

Num(Date#( '01-02-2016' ,'MM-DD-YYYY'))

regards

Marco

[AH]

Thanks Marco!

Regards,

Ahmed100

[jagan]

HI,

Try like this

LOAD

*,

Num(Date#( DateFieldName ,'MM-DD-YYYY')) AS DateFieldName_Number

FROM DAtaSource;

If above not working then try this

LOAD

*,

Num(DateFieldName) AS DateFieldName_Number

FROM DAtaSource;

Hope this helps you.

Regards,

Jagan.

[AH]

Hi Jagan,

I also have two other conversion to take care:

1.  Convert Date field format '01-02-2016'  to '01/02/2016' format and

2. Convert Date field format '41640'  to '01/02/2016' format

Thanks in advance.

Regards,

ahmed100

[jagan]

HI,

Try like this using Alt() if both dates are in single column

LOAD

*,

Date(Alt(Date#(FieldName, 'MM-DD-YYYY'), FieldName) AS NewField

FROM DataSource;

Regards,

Jagan.

[AH]

Hi Jagan,

Thanks for your reply. But They are different fields.

Regards,

ahmed100

[jagan]

HI,

Then try like this separately

Date(Date#(FieldName1, 'MM-DD-YYYY'))

Date(FieldName2)

Regards,

Jagan.

[AH]

Hi Jagan,

Could you show me, how To convert date format '41640' to date format 'YYYY-MM-DD' ?

Thanks,

ahmed100

[jagan]

Date(FieldName2, 'YYYY-MM-DD')

Regards,

Jagan.