File Reader (Complex Format) V 4.5 Nightly Conversion to Date

[izaychik63]

I have a CMS Open Payment file with date column in format mm/dd/yyyy. Date is recognized as string. An attempt to LocalDate is not working, format field is grayed.
Could this conversion be supported?

[ipazin]

Hello @izaychik63,

don’t know much about CMS Open Payment files. Do they store column type and format? If not you would need a place to input a proper format in reader node so he can parse it which is actually a functionality from String to Date&Time node so don’t see that one happening.

Br,
Ivan

[izaychik63]

Here’s the CMS site https://www.cms.gov/OpenPayments/Data/Dataset-Downloads

[ipazin]

Hello @izaychik63,

those files are csv so don’t think there is a way for such functionality as explained above.

Br,
Ivan

[izaychik63]

Thank you, Ivan. In this case it will be reasonable to include String to Date function in Column expression.

[ipazin]

Hello @izaychik63,

there already is. It’s called Date() function. If your string complies with ISO-8601 (yyyy-mm-dd format) then you can simply pass your column as argument. If not (and if I remember correctly from above linked data format you have does not) then you’ll need to pass year, month and day separately as number arguments but this should be an issue using substr() function inside toInt() function.

Give it a try and if any issues I can create an expression for you. (Only share your format please.)

And why not simply using String To Number node if I may ask?

Br,
Ivan

[izaychik63]

Thank you, Ivan. I looked for toDate() function as others conversion one and missed Date() one.