Select last dates of each SQL month

Question

I would like to perform an SQL to get from a list of dates the last day of each month.

---

Example: From a list 01/01/2016 until 31/12/2016 return the last days of each month: 31/01/2016, 28/02/2016... 31/12/2016.

The database is in Filemaker.

Answer 1

In PostgreSQL you can use it like this:

SELECT TO_CHAR(data, 'YYYY-MM-01')::DATE + 
       (INTERVAL'1 MONTH' - INTERVAL'1 DAY') AS data
FROM tabela
GROUP BY data

At a glance in that documentation.

Answer 2

The SQL Server 2012 forward you can use the following:

SELECT EOMONTH(Data) from Datas

In other versions of SQL Server you can do something like this:

select DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,Data)+1,0)) from Datas

In Oracle:

select LAST_DAY(Data) from Datas

Answer 3

Another way to perform this query, only with calculations:

SELECT 
   DATEADD(month, ((YEAR(data) - 1900) * 12) + MONTH(data), -1) 
FROM
   Datas

See working: Sqlfiddle

Answer 4

In filemaker I could not get any Generica solution via SQL, the way was to generate several queries for the period using the OFFSET and FETCH FIRST clauses

Answer 5

In Filemaker, the function date(Month;day;year), by displaying 0 in the parameter day, returns the last day of the previous month. For example, date(2;0;2016) returns the last day of January 2016.

The combined use of functions date(), year() and Month() allows to obtain the last day of the date month of the table. Assuming the column name is Datasale, we may have something similar to date((month(DataVenda) +1); 0; year(DataVenda))