How to add a months to given date

Solved!
dakakiran
Level 1
How to add a months to given date

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

1 Solution
tgb417

@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

--Tom

View solution in original post

4 Replies
tgb417

@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

--Tom
dakakiran
Level 1
Author

Thanks Tom , it worked like a charm.:)

somehow i missed this function

tgb417

@dakakiran ,

Excellent,  so glad I could help.

I also missed the function several times.  

inc is just not very intuitive to me.

--Tom

--Tom
ahjimomo
Level 1

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. 

Labels

?
Labels (2)
A banner prompting to get Dataiku