Date Format

[areeba]

Hi

I have one requirement in which I have few date columns whose format is '2020-12-31' and data type is STRING but I want to parse them into '31-12-2020' format as DATEdatatype . I am using Multi Field Formula for this but not able to write the correct expression. Can you please help me ?

For eg:

Input:

Contract Date     Hiring date

2020-12-31          2020-8-3

I need to have Output:

Contract Date    Hiring Date

31-12-2020         3-8-2020

Also I have one column called Value  which has following data :

Input:

Value

1.1111

0.8889

0.75

0.0278

I need to have output as

Value

1,1111

0,8889

0,75

0,0278

This value has DOUBLE datatype.

Thanks

Areeba

[PhilipMannering]

Hi @areeba 

Bear in mind that if you want Alteryx to recognize dates as a date data type, then it has to be in the yyyy-mm-dd format. See solution attached,

[Qiu]

@areeba 
@PhilipMannering answered your first one.

[areeba]

Hi

Thanks for quick reply but this solution giving error "Type mismatch. Number provided where a string is required". Its the same error which I was having. Can you tell what I am missing?

Also I see still a dot in Value field (1.111)instead of comma (1,111)

Regards

Areeba 

[areeba]

Hi @Qiu 

Thanks for the solution but the thing is i cannot change the datatype of Value which is DOUBLEto V_WSTRING because for further calculations i need to round the value. The Replace function is applicable to string values.

Can you suggest a solution in which I do not have to change the datatype and get the comma value(1,111)

Regards

Areeba

[PhilipMannering]

Hey,

If you're converting the data type you need to save the new value as a new field.

Just to reiterate, if you have your dates in a non yyyy-mm-dd format and numbers using a comma instead of a fullstop, these values can only be stored as strings. And in the formula tool you need to save as a new field to change the data type. Solution is attached to help.

[AkimasaKajitani]

Hi @areeba 

Answer 1:

"2020-12-31" is Alteryx standard date type format.

But "2020-8-3" is not Alteryx standard date type format.
So you have to change type from "String" to "Date" format by DataTime tool or Formula tool using DateTimeParse function.

And then you can change the required format by DataTime tool or Formula tool using DateTimeFormat function.

OR

You can use Text To Columns tool. you may change the type to String and split at delimiter is "-".

And then you may concatenate the 3 columns by formula tool.

Alteryx Designer only treats Alteryx standard formats("yyyy-mm-dd") as date types.

Others are all "String" type.

Answer 2:

It seem the same that you provided. But you want to change the type  to Double?

It is possible with the Select tool.

What looked like a dot was a comma, my apologies.

 

[Qiu]

@areeba 
The comma will force the field to be string. so we have to have new column for this.

[areeba]

Hi @AkimasaKajitani 

Thank you for the solution but like I mentioned I have (few) 7 to 8 columns which have dates with the format  2020-12-31 for which I am using Multi Field formula because I have to adjust all 8 columns to format

31-12-2020 from 2020-12-31.

Can you help in that ? 

Thanks 

Areeba

[AkimasaKajitani · Accepted solution]

Hi @areeba 

I have recognized that you want to change multiple fields into required field.

You can use Multi-Field Formula.

Firstly I recommend you change the field type to String by Select tool.

An then you have to use Multi-Field Formula at below setting.

DateTimeFormat(DateTimeParse([_CurrentField_],"%y-%m-%d"),"%d-%m-%Y")

Please refer attached file.