How to add a months to given date

Options
dakakiran
dakakiran Dataiku DSS & SQL, Dataiku DSS Core Concepts, Registered Posts: 3 ✭✭✭✭

Hi Team.

Is there a dataprocessor or function to add months to a given date.

Something like for ex : dateadd function in SQL.

ex 1: for todays date(24-Aug-2020), i would like to add 4 months then it would be 24-Dec-2020

ex2: for todays date(24-Aug-2020), i would like to add - 4 months then it would be 24-Apr-2020.

I know a way that if we use SQL recipe, we can define database functions to get the result,but i am looking some processor or function in datailku

Best Answer

  • tgb417
    tgb417 Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, 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: 1,595 Neuron
    edited July 17 Answer ✓
    Options

    @dakakiran

    It is not intuitive how to do this, if you know SQL.

    However, you can use a formula step in a visual prepare recipe to make these changes.

    You are looking for the inc (increment function) in the Formula.

    for example

    inc(LastUpdated, 4, "month")

    Will add 4 months to a date in LastUpdated column if that column has been parsed to a DSS standard format with the date parse command.

    Increment a Date.jpg

    Hope this works for you.

    You can find the documentation on the date formula functions here

    https://doc.dataiku.com/dss/latest/advanced/formula.html#date-functions

Answers

  • dakakiran
    dakakiran Dataiku DSS & SQL, Dataiku DSS Core Concepts, Registered Posts: 3 ✭✭✭✭
    Options

    Thanks Tom , it worked like a charm.:)

    somehow i missed this function

  • tgb417
    tgb417 Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, 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: 1,595 Neuron
    Options

    @dakakiran
    ,

    Excellent, so glad I could help.

    I also missed the function several times.

    inc is just not very intuitive to me.

    --Tom

  • ahjimomo
    ahjimomo Dataiku DSS Core Designer, Registered Posts: 1
    edited July 17
    Options

    I have also encountered this question, and just as a reference for alternative solution. If anyone prefers to use a `filter` recipe, you can choose to `filter with formula` instead.

    The following script should enable you to perform the filtering of any length of date preferred:

    # Filtering any records from past 6-months
    trunc(date_column, 'month') >= inc(trunc(now(), 'month'), -6, 'month')

    Notes:

    • `date_column` refers to the column you are filtering on, note that it has to be in the DSS date format
    • `trunc()` function enables us to truncate the data to it's month
    • `inc()` function is equivalent to SQL's `dateadd()`, where you want to get the month 6 months ago for the comparison.

    p.s. My background is on Python thus pardon on the formatting.

Setup Info
    Tags
      Help me…