EPOCH_TIME_WIN32 always returns the same date

[lawleym (Mark Lawley)]

I am trying to convert a date to EPOCH_TIME_WIN32 format using a transform. No matter what I do, the date is always 133800768000000000 which equates to 29 March 1974. The date should be 31 December 2024.
The transform is:

{
    "name": "Account Expires Date",
    "type": "firstValid",
    "attributes": {
        "values": [
            {
                "type": "dateFormat",
                "attributes": {
                    "input": {
                        "attributes": {
                            "inputFormat": "yyyy-MM-dd",
                            "outputFormat": "ISO8601"
                        },
                        "type": "dateFormat"
                    },
                    "inputFormat": "yyyy-MM-dd'T'HH:mm:ss.SSSZ",
                    "outputFormat": "EPOCH_TIME_WIN32"
                }
            }
        ]
    },
    "internal": false
}

Is anybody able to identify what the problem is?

[vdivakar (Vaibhav Divakar)]

Hi @lawleym , you have not mentioned input attribute, I mean,
You need to explicitly mention in the transform, which attribute you are taking the date, and then convert that date using dateFormat transform.

Since your transform is multilevel, you need to define which attribute you are taking the date from and then convert that date and then give the entire output of that transform to FirstValide transform.

Check the corrected transform below

{
    "name": "Account Expires Date",
    "type": "firstValid",
    "attributes": {
        "values": [
            {
                "type": "dateFormat",
                "attributes": {
                    "input": {
                        "attributes": {
                            "attributeName": "<source attribute name>",
                            "sourceName": "<name of the source from which attribute is being read>"
                        },
                        "type": "accountAttribute"
                    },
                    "inputFormat": "yyyy-MM-dd'T'HH:mm:ss.SSSZ",
                    "outputFormat": "EPOCH_TIME_WIN32"
                }
            },
            {
                "type":"static",
                "attributes":{
                    "value":"No Date"
                }
            }
        ]
    },
    "internal": false
}

The transform will return “No Date” as static text, if it does not process the date correctly using DateFormat transform.

Please let me know if this helps

Thanks,
Vaibhav

[phil_awlings (Phil Rawlings)]

Why are you formatting the date twice?

[sk8er23 (Anthony Huxtable-Jones)]

Something like should work as a transform applied to identity attribute mapping

{
“name”: “Calculate Date Format”,
“type”: “firstValid”,
“attributes”: {
“values”: [
{
“type”: “dateFormat”,
“attributes”: {
“inputFormat”: “yyyy-MM-dd’T’HH:mm:ss.SSSZ”,
“outputFormat”: “EPOCH_TIME_WIN32”
}
}
]
},
“internal”: false
}

I think you issue is the 2 different formats you are trying to define

[lawleym (Mark Lawley)]

Thank you to everybody who has replied.
The date is formatted twice because on the source the format is yyyy-MM-dd. We tried doing this directly to EPOCH_TIME_WIN32 and it gave us the date in 1974 even though the original value is 2024-12-31. We tried adding the intermediate step of converting to ISO8601 format to see if it fixed the problem.
I have tried the suggested transform and still get the same answer.

[sk8er23 (Anthony Huxtable-Jones)]

I haven’t tested this:

{
    "name": "Account Expires Date",
    "type": "firstValid",
    "attributes": {
        "values": [
            {
                "attributes": {
                    "input": {
                        "attributes": {
                            "inputFormat": "yyyy-MM-dd",
                            "outputFormat": "ISO8601"
                        },
                        "inputFormat": "yyyy-MM-dd'T'HH:mm:ss.SSSZ",
                        "outputFormat": "EPOCH_TIME_WIN32"
                    },
                    "type": "dateFormat"
                }
            }
        ]
    },
    "internal": false
}

[phil_awlings (Phil Rawlings)]

Also, as @vdivakar mentioned, in your original transform, you don’t have an input value. Try this, but amend the attributeName & sourceName accordingly:

{
  "type": "dateFormat",
  "attributes": {
    "input": {
      "type": "accountAttribute",
      "attributes": {
        "attributeName": "end date",
        "sourceName": "Test source"
      }
    }
  },
  "inputFormat": "yyyy-MM-dd",
  "outputFormat": "EPOCH_TIME_WIN32"
}