Hi, I have a date field in my model . When trying to load data, I get this

The data in the 'end_date' column cannot be converted to 'DATE' format.

This column is date but can contain null. How can I tell the data model that it could be null?

Hi Dilaksha, you will need to remove the null values from the date column so the platform can load is as a date dataset. You can check the file requirements in our documentation. If you wish to work with the null values, then you can load as a text data type.

thanks @Moises Morales, my use case is we have a start date and end date for say customers. The start date is when they sign up, the end date is when they leave. So all current customers do not have an end date. This seems to be a major limitation as we still want to treat end date as a date so we can create date-based insights. If I use this as a text column, I can't do this?

I am quite sure it is possible to load empty/null date to your workspace. Can you please elaborate how exactly are you loading the data (whether from a data modeller or with Automated Data Distribution and from which source or some other way)? In case you would be loading it from a file, can you please provide an example how the date data look like (i.e. an example of a normal date field and how the null looks like). And if you could provide also the request_id from the error we can try to check the logs on our end.

Hi @Michal Hauzirek, Here is the log info

2023-04-24T18:09:43.670+0200 [INFO]: Request id: "data_load_nAndwqDJr1_9MjE4LGohq:XjMamOClAJaU1ZMR:DTG1P4wbh53fi0dS"

error

2023-04-24T18:09:49.577+0200 [INFO]: dataset: dataset.rental_product_inventory, Messages:["The data in the 'end_date' column cannot be converted to 'DATE' format."]

The LDM column is yyyy-MM-DD format sample data in the csv is

NULL

Thank you very much for all the details. I did not find much more in the logs, but I did some testing and I believe now I know where the problem is. It is really the

NULL

value in the S3 file. If you want to load empty date from a CSV file, it needs to be represented by an empty string (i.e.

,,

in the CSV, not

,NULL,

). The field will still be a date field. Please see the attached sample file. When I used NULL, I got the same error as you. When I used empty string (just two comas in the CSV - lines 6,7 and 11), I was able to load the data and they work as empty date in GoodData. I will try to ask the GoodData doc team to add this info explicitly to the documentation, since using NULL as you did might be quite common and the error message is not that helpful in this case.

Thanks @Michal Hauzírek, I would think so too. When you extract data from RDBMS to CSV, most tools will output NULL than empty string. This means more ETL interruptions in our side but thanks for figuring that out for us, appreciated