0

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.

RustyShackleford
  • 3,462
  • 9
  • 40
  • 81

3 Answers3

3

function 'LAST_DAY' worked for me.

Used like this:

LAST_DAY(date_field)
RustyShackleford
  • 3,462
  • 9
  • 40
  • 81
  • I thought this was going to be really messy in Dremio, LAST_DAY seems to work... this should be marked as answer. – jhowe Feb 14 '23 at 16:07
1

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.

Eric Brandt
  • 7,886
  • 3
  • 18
  • 35
0

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

Sam J
  • 1