Skip to content
+1-888-319-3663

COMMUNITY FORUM

Create a calculated field to return the last day of the month

Karen asked 8 months ago
My source is a SQL query that includes columns for Month and Year.  How can I use that information in a calculated field to return the last day of the month in date format? 
Alternately, can I use the Date.Now to return the last day of the previous month?
 
 
Answers
Patrick Roth Staff answered 8 months ago
Since you have a SQL Query already, you could use SQL then to take your Month & Year columns and turn them into the first day of the month
https://stackoverflow.com/questions/266924/create-a-date-from-day-month-and-year-with-t-sql
In the above example, they use a few different ways to get a date or datetime value created from multiple fields.  In your case, your “day” is just hard coded as 1.
so month = 12 & year = 2020
would give you 12/1/2020.
From there the easiest way to get the “last day” is to add a month which gives us 1/1/2021 (so the first day of the next month).
Then take away a day which ends up giving you the last day of the previous month (which is what we want) of 12/31/2020.
https://stackoverflow.com/questions/1051488/get-the-last-day-of-the-month-in-sql
I don’t have a table with a month & year in it so I’m hard coding here:
 
select DATEFROMPARTS(2020, 12, 1), DateAdd(d,-1,DateAdd(“MM”,1,DATEFROMPARTS(2020, 12, 1)))
 
So here I’m using the example above in that I hard coded 12 & 2020 – those would be your fields from your table.  
With the above code, it would return:
12/1/2020  & 12/31/2020
 

If you would like to submit an answer or comment, please sign in to the eOne portal.