Sign up to take part
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
Autodetect datetime column for SQL output
Added on May 7, 2021 8:09AM
Likes: 0
Replies: 2
JohnB
Registered Posts: 32 ✭✭✭✭✭
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.
Answers
-
Ignacio_Toledo Dataiku DSS Core Designer, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 416 NeuronHi @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.
-
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