Workato bug? The to_date function assumes a 4 digit year and ignores input format

[RussellJ]

Hello Forum People

So, I’ve got a STRING value of… "26/03/26 14:51:06" from one system that I need to convert to a date for use when making an API call to another system. According to the Workato documentation  the .to_date() function is what I want.

The “input format” of my sample string is obviously DD/MM/YY.  The documentation is very clear that the format parameter for to_date “defines only the input formula”.

But when I try to use - MYSTRING.to_date(format: "DD/MM/YY") Workato throws an error – “Error calculating input for field 'DateConv': invalid date”.

If I use - MYSTRING.to_date(format: "DD/MM/YYYY") it works although obviously I'm lying to it about the format being passed in being a 4 digit year.  However, the result it gives me is 0026-03-26.

So having insisted on a 4 digit year format it then treats my 26 as in the first millennium.  So it looks like I'd then have to add 2000 years to arrive at the correct year if I have a 2 digit year format!  The following works and gives me the result that I want (but I need a long shower afterwards)...

(I have no control over the string being passed in.  Historically the format has been as per the sample, but it worries me that it might change in future).

But most importantly, this feels like a Workato bug in that the to_date function is ignoring the input format I specify, but is assuming a four digit year?

Thoughts on the possible bug?  Suggestions as to how best to convert my string to a date?  I could split up the string but then I'm making assumptions again about the input format being consistent.

Thanks for any insights offered in advance!

Russell

[gary1 · Accepted solution]

I think you need this:

https://systematic.workato.com/t5/workato-pros-discussion-board/need-to-change-date-format/m-p/5503

[gary1]

Also, I don't think it's a bug. Having a two-digit year simply isn't enough data to accurately determine the correct century. Like a "50" could legitimately be 2050 or 1950, and there's simply no way to know the difference without additional context.

[RussellJ]

Hey Gary

Thanks for your replies (as always)!

I completely understand that any system when faced with a two digit year has to make assumptions.  And (especially since the fuss around Y2K) we all know how bad that can be.  But the documentation tells me that the function respects my input format.  And it doesn't.  And there's no mention of a 4 digit year requirement.  So I'm sticking with FAIL on that 😀

Appreciate your link to an earlier post - apologies, I should have searched the Forum first!  Interesting you also suggest adding 2k years as a workaround.  But it don't feel right! 😂 

Cheers

Russell

[gary1]

Maybe I’m missing something, but the documentation says that the format is .to_date(format: "%m/%d/%y") and not “MM/DD/YY”