Autodetect datetime column for SQL output

JohnB

For automatically generated datasets writing to a SQL table, is there a way to force a datetime type for the column - apart from setting it to manually defined?

For example, a recipe that has an input dataset that is string, date(unparsed) results in a SQL Server column that is nvarchar(max) and not datetime.

Ignacio_Toledo

Hi @JohnB
,

From your example, date(unparsed) (the meaning detected by dataiku) is just telling you that most probably the column is a date but you'll need to parse into a format that can be recognized as such by the target database.

I think it is not possible to "force it" because dataiku just makes an educated guess of the meaning, but one have to be sure that the parsing is done correctly.

For example, let's say we have column with dates with format '2021-12-31'. Dataiku will show the meaning "date(unparsed)", but what if the dates in the first row were '2021-01-02'? Is that February 1st or January 2nd? And what about the time zone? The only way the string could be automatically stored into a datetime column would be if the string has the date in ISO 8601 format.

Hope this helps... I'm no so sure if my writing here is clear enough.

JohnB

Hi Ignacio,

Thanks for your reply. Your writing is very clear.

The dates in a typical input dataset seem to be in this format:

2021-03-11T20:08:34.000Z

Regards,

John