Need to convert date to epoch time but it is converting to the 1900's

[kensowens]

Hi I am trying to compare two dates. I am trying to find out if date A is larger than Date B. I have included my jsonata attempt below but the first date is coming back in the 1919 instead of 2019

Please see my code here with input.

https://try.jsonata.org/7KTKDf5v9

[ab73863]

Hi @kensowens ,

First of all the date string provided in the sample input is the 2 digit format of year YY. In such cases $toMillis() function will default to 1900s as w.r.t epoch. This is the reason why you are getting the 1900s in the year part of your converted date string. And this is the same reason why the filtering was failing to check the date logic.

You have two option to deal this

Option 1 : The input to have 4 year digit format i.e DD-MM-YYYY. In this case your existing filter logic will work without any issues. No further changes required.

Option 2 : In case if you are not have the flexibility to make the change on the input then you can use the following JSONata expression.

$filter(msg.payload,function($v,$i,$a){
    $toMillis( $replace($v.Date,/[^\/]+$/,'20' & $substring($v.Date,6)),'[M]/[D]/[Y]' ) > 1502629867848
})

Working sample : https://try.jsonata.org/bXuYXzDOR

How the Option 2 expression works

1. we are getting the two digit year part from the date string using $substring($v.Date,6)),'[M]/[D]/[Y]
2. Replacing the two digit year part in the date string by appending with '20' in order to make its as four digit. $replace($v.Date,/[^\/]+$/, '20' & $substring($v.Date,6))
3. Now finally we convert the newly constructed toMillis for checking the greater than logic. $toMillis( $replace($v.Date,/[^\/]+$/,'20' & $substring($v.Date,6)),'[M]/[D]/[Y]' ) > 1502629867848

Hope this answers your problem statement.

[kensowens]

This solved my problem thanks