I would like to perform a SQL to get a list of dates on the last day of each month.
Example: From a list of 01/01/2016 until 12/31/2016 return the last days of each month: 1/31/2016, 2/28/2016 ... 12/31/2016.
The database is in FileMaker.
Select last dates for each month SQL
2
asked by anonymous 03.10.2017 / 21:52
5 answers
0
In the filemaker I did not get any generic solution via SQL, the way was to generate multiple queries for the period using the OFFSET and FETCH FIRST clauses
17.11.2017 / 22:10
2
In PostgreSQL you can use this way:
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 this documentation .
03.10.2017 / 22:23
2
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
03.10.2017 / 22:48
2
Another way to perform this query, only with calculations:
SELECT
DATEADD(month, ((YEAR(data) - 1900) * 12) + MONTH(data), -1)
FROM
Datas
See it working: SQLFiddle
04.10.2017 / 14:08
0
In FileMaker, the date (month; day; year) function , when 0 is entered in the day parameter, returns the last day of the previous month. For example, date(2;0;2016) returns the last day of January 2016.
Combined use of date () , year () and month () allows you to get the last day of the table date month. Assuming the column name is DateTable, we can have something similar to date((month(DataVenda) +1); 0; year(DataVenda))
04.10.2017 / 10:50