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

0
votes

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"
    }
]
2
datedatetimemuledataweave
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
@mhery Is the input a date or a string? – aled
@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
@aled, the input is a string, it is a response of other API – mhery

2 Answers

2
votes

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"}
}
2
votes

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" }