Data360 Analyze

Hi All,

I have been trying to convert a datetime column from the format of CCYY-MM-DD time to CCYY/MM/DD.

I tried to use the below code in the Process Records of the transform node but it did not work

out1['Maturity Date']= datetime.strftime('%m/%d/%Y')

Kindly help me out on this

You do not provide details of the error but there are probably a couple of issues with the above code. The first is that you do not reference the input field on the right hand side of the assignment. Secondly, the code will not handle the situation where there are Null values in the input data.

As an example of using a Transform node for the conversion, assume the input data is as follows:

The ConfigureFields script would comprise:

In this case, as the output formatted date string field is to have the same name as the input field (which is a Date type), it is necessary to remove the existing field from the output data. The metadata for a new unicode string field can then be added.

The ProcessRecords script would perform the conversion of the values:

The code tests whether the input value is Null and, if it is, sets the output value to Null. Otherwise, the input Date value is converted to the formatted string.

You can also achieve the above in a simple and code-free manner using the Modify Fields node. 

The output Type is configured to be unicode and the required string format is selected from the options listed in the Format attribute (you can also enter/edit the format pattern if a custom format is required). The Modify Fields node automatically handles any Null values in the data.

The attached data flow contains the above example (Analyze v.3.6.x required).

Attached files

Convert to Formatted Date String - 22 Feb 2021.lna

Hi Adrian,

Thank you for the help, your recommendation has worked. 

Hi Adrian, I've been trying to transform this date format and not yet find a solution for it, pls can you help out.

Aug 31 2020 23:57:32 +13

which I want to transform it to the format yyyy-MM-dd

You can use the Modify Fields node to convert the string into a Datetime type. A Format pattern needs to be specified to provide a hint to the node.

The Help documentation for the Modify Fields node provides a link to further information on the datetime formatting patterns used by Java. 

Once the data has been converted to a Datetime type you can then use a further Modify Fields node to convert to a date type:

or, if required, to a string representation of a date value:

An example data flow is attached below (requires Analyze v.3.6.x)

Attached files

Convert Datetime to Date Example - 8 Mar 2021.lna