Convert date with format EEE MMM dd HH:mm:ss zzz yyyy to dd/MM/yyyy in dataweave 2.0

Question

I have some SAP dates with EEE MMM dd HH:mm:ss zzz yyyy format. For example, Mon Sep 02 00:00:00 BST 2019. I need to transform it to dd/MM/yyyy using dataweave 2.0, but until now I had no sucess.

I thought that this transformation might run well, but it isn't:

%dw 2.0
output application/json
---
{
    timestamp: "Mon Sep 02 00:00:00 BST 2019" as LocalDateTime {format: "EEE MMM dd HH:mm:ss zzz yyyy"} >> "BST" as String {format: "dd/MM/yyyy"}
}

The data I have to transform is a little bit more complex:

[
    {
        "id": 1,
        "timestamp": "Tue Jan 06 00:00:00 BST 2000"
    },
    {
        "id": 2,
        "timestamp": "Sun Dec 05 00:00:00 BST 2015"
    },
    {
        "id": 3,
        "timestamp": "Mon Oct 04 00:00:00 BST 2017"
    },
    {
        "id": 4,
        "timestamp": "Sat Jul 03 00:00:00 BST 2020"
    },
    {
        "id": 5,
        "timestamp": "Mon Sep 02 00:00:00 BST 2019"
    }
]

Try double quoting the timestamp: %dw 2.0 output application/json --- { timestamp: "Mon Sep 02 00:00:00 BST 2019" as LocalDateTime {format: "EEE MMM dd HH:mm:ss zzz yyyy"} >> "BST" as String {format: "dd/MM/yyyy"} } If it doesn't work, please include an input payload example in your question. Thanks — olamiral, Oct 15 '20 at 12:54
@olamiral, my mistake. I already tried with double-quotes. I just added an input payload, but I tried first to be more simple and made the transform with the string in the example. — mhery, Oct 15 '20 at 13:07

score 2 · Answer 1 · answered Oct 15 '20 at 13:17

You need to convert the string to a date, using the input format, then convert the date to string with the output format. Note that the input has a timezone, so it can not be a LocalDateTime.

%dw 2.0
output application/json
---
"Mon Sep 02 00:00:00 BST 2019" as DateTime {format: "EEE MMM dd HH:mm:ss zzz yyyy"} as String { format: "dd/MM/yyyy" }

Your input has incorrect days of week. I fixed as:

[
    {
        "id": 1,
        "timestamp": "Thu Jan 06 00:00:00 BST 2000"
    },
    {
        "id": 2,
        "timestamp": "Sat Dec 05 00:00:00 BST 2015"
    },
    {
        "id": 3,
        "timestamp": "Wed Oct 04 00:00:00 BST 2017"
    },
    {
        "id": 4,
        "timestamp": "Fri Jul 03 00:00:00 BST 2020"
    },
    {
        "id": 5,
        "timestamp": "Mon Sep 02 00:00:00 BST 2019"
    }
]

To parse each timestamp and return an array you can use:

%dw 2.0
output application/json
---
payload map $.timestamp as DateTime {format: "EEE MMM dd HH:mm:ss zzz yyyy"} as String { format: "dd/MM/yyyy" }

thanks for your response. with first transformation, I got `Cannot coerce String (Mon Sep 02 00:00:00 BST 2019) to DateTime, caused by: Text 'Mon Sep 02 00:00:00 BST 2019' could not be parsed at index 0`. With second, almost same error: `Cannot coerce String (Thu Jan 06 00:00:00 BST 2000) to DateTime, caused by: Text 'Thu Jan 06 00:00:00 BST 2000' could not be parsed at index 0`. I am using Studio v7.5.0 for this project. — mhery, Oct 15 '20 at 13:26
Works in my test. Are you seeing that in the preview or when executing the application? And which version of Mule? Please also try with Studio 7.6.0, the current version. — aled, Oct 15 '20 at 14:49
the problem is happening because of my locale settings, which are not en/us. olamiral helped me with this in his answer. thanks for your response! — mhery, Oct 15 '20 at 19:11

score 2 · Accepted Answer · answered Oct 15 '20 at 13:21

2

The problem you are getting is because some of the dates in the input payload are not valid. Check this question for further details: Caused by: java.time.DateTimeException: Conflict found: Field DayOfWeek 6 differs from DayOfWeek 2 derived from 2016-01-30

If you fix the days of the week of the invalid entries, the dataweave expression will work as expected (make sure that the day of the week corresponds to the actual day of the week for each timestamp).

Also, make sure you are converting the timestamp to DateTime as it contains a timezone:

%dw 2.0
output application/json
---
payload map (item, index) -> 
{
    timestamp: item.timestamp as DateTime {format: "EEE MMM dd HH:mm:ss zzz yyyy"} as Date {format: "dd/MM/yyyy"}
}

answered Oct 15 '20 at 13:21

olamiral

1,296
7
8

I tried to use the Monday date of example, which is correct, but got the same error of aled response: `"Cannot coerce String (Mon Sep 02 00:00:00 BST 2019) to DateTime, caused by: Text 'Mon Sep 02 00:00:00 BST 2019' could not be parsed at index 0` – mhery Oct 15 '20 at 13:35
Could you please confirm what is the locale / language configured in you operating system? – olamiral Oct 15 '20 at 14:18
locale: Brasil, language: Português (Brasil) – mhery Oct 15 '20 at 14:24
1

Ok. Try changing your eclipse locale / language to en us. Or convert Mon and Sep to the equivalent in that locale (Seg for Mon and Set for Sep, for example), and see if the timestamp gets correctly parsed. To change your Anypoint Studio locale, check this question: https://stackoverflow.com/questions/4947484/how-to-set-eclipse-console-locale-language. You can check this article, too: https://help.mulesoft.com/s/article/DataWeave-Cannot-Coerce-a-string-To-a-localdatetime-When-Using-Non-English-Regional-Settings-Locale-Settings-On-A-Host-OS – olamiral Oct 15 '20 at 14:27
this problem is exactly what is occurring here. I inserted the transformation inside DW Playground, and it ran well. However, both docs you sent to me did not helped. I'll try to figure out what is going on with more caution, and maybe open a question about it. Thanks a lot. – mhery Oct 15 '20 at 19:09
Have you tried setting the locale? As the date contains information that changes based on the locale (day of week and month), the runtime locale must match the locale / language corresponding to the payload. – olamiral Oct 15 '20 at 19:13
1

Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/223110/discussion-between-olamiral-and-mhery). – olamiral Oct 15 '20 at 19:14

Convert date with format EEE MMM dd HH:mm:ss zzz yyyy to dd/MM/yyyy in dataweave 2.0

2 Answers2