Autodetect datetime column for SQL output

JohnB
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
    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: 415 Neuron

    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
    JohnB Registered Posts: 32 ✭✭✭✭✭

    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

Setup Info
    Tags
      Help me…