Last date and first date of the month

[GD88]

How do I find the last date and first date for a given month?

[Trifacta_Alumni]

Hi - There is no direct function to do this. However, you'll need multiple steps to achieve this.

1. UNIXTIME() function on date columns. You need this inorder to carry out operations. This creates a numerical value of number of milliseconds since Jan 1st 1970 i.e, Unix time inception.
2. Find MONTH or MONTHNAME() from date column dropdown or toolbar or search function
3. Group by task from toolbar,group by field Month/Monthname and values: MAX(Unixtime), Minx(Unixtime)
4. Convert the unix time format back to datetime format using UNIXTIMEFORMAT() function

Hope this helps

Best

Vardan

[Trifacta_Alumni]

@Gabby Deal? Does this answer your query or is it something else you are looking for?

[GD88]

Thanks for the work around approach. I wish there was a simple transformation to do this instead of doing in so many steps. Can't Trifacta add this?

[Trifacta_Alumni · Accepted solution]

You can create a Macro of the 4 steps I described and use them across the flows. That way you don't have to write these steps and just use 1 step to invoke the Macro. However, i'll take your request as a feedback and channel it across to Product Management team.

[Trifacta_Alumni]

@Gabby Deal? I forgot to mention this, but you can also choose multiple First Day on Month and Last Day Macros from our public macros repo.

Here's the link https://community.trifacta.com/s/wrangle-exchange

You can also navigate from the tool and select ? --> Wrangle Exchange