Custom datetime format in Object Schema

Enrico Menotti

When defining an Object Schema in Data Catalog in ION to be used in Data Lake (I'm doing this manually at the moment, not by sample file), it is possible to choose the Data Type for each field. In particular, an option is available to set it to Date Time, and a sub-option the Date Time Format may be defined.

I have tested with the Standard Date Time Format and the American one, and on Data Lake it works fine. I'm now trying to define a Custom Format in the European Style, I'd say:

dd/MM/yyyy hh:mm:ss

This doesn't work: on Compass, nothing (= null) is returned for the field. Furthermore, if I try 

EXEC INFOR.CLEAR_TABLE('Project', 'True')

I get "An internal error occurred". I have also tried to use the American format and simply double the day, month and hour placeholders, i.e.

MM/dd/yyyy hh:mm:ss[.S] a

This also gives the internal error.

Does anybody know what I'm missing here?

Enrico Menotti

Hi Kevin,

Thanks a lot, by starting from your second link I found the answer. Namely, your link goes to "Using datetime formats", which has a sub-topic "Custom datetime formats", and there I read:

"Compass queries only support the date and datetime formats that are displayed in the table below. Any other formats, or variations of a custom format, are not supported."

Only 5 Custom Formats are available. Two of them are for the American usage. None for the European. Quite reductive for a product supposed to be used worldwide.

BTW, I was doing CSV, but I think that doesn't matter.

I suggest my reply as an Answer, although lots of credit should be given to your reply.

Thanks and best,

Enrico