CODEWITHSUNDEEP
sqldremio
RustyShackleford
RustyShackleford

Reputation: 3677

How to calculate last day of month per row in dremio?

I have a properly casted date column that I want to create a new column from that is that date of the last day of the month.

Example:

current_datefield         end_of_month_date
2019-03-01                 2019-03-31   
2020-01-29                 2020-01-31

I dont know how to do this in dremio, I have tried EXTRACT,Date_PART

Non of the solutions work. Thank you in advance.

Upvotes: 1

Views: 2004

Answers (3)

RustyShackleford
RustyShackleford

Reputation: 3677

function 'LAST_DAY' worked for me.

Used like this:

LAST_DAY(date_field)

Upvotes: 4

Sam J
Sam J

Reputation: 1

If you are SQL Server, there is a function called EOMONTH. Using the EOMONTH function, you can write a query for your requirement. Check out this youtube video providing solution to your requirement: https://youtu.be/QsQDHux1C4A

Upvotes: 0

Eric Brandt
Eric Brandt

Reputation: 8101

Since Dremio is proprietary, and we don't run it where I work, I can't test specific code for you, but I can explain the approach. I'll leave working out the exact syntax to you.

Broadly speaking, the solution is to use the date value that you have to calculate the first day of the next month, then subtract one day. This works because every month has a first of the month, and every SQL engine will get the correct EOM date when subtracting one from the first. In most systems, there are several ways to do that.

The sort of "brute force" way is to take your current date, current_datefield, then EXTRACT the year and the month. Add one to the month. Use those values and a hardcoded 01 to "build" the first of next month. There will probably be a TO_DATE or some such involved there.

Now just wrap that value in a DATE_ADD function to subtract one.

Upvotes: 1

Related Questions