Select last dates of each SQL month

Asked

Viewed 2,617 times

2

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.

  • I don’t know if it exists but if you sort your search wouldn’t help? Type 'order by id desc'

  • 2

    which database ?

  • Order by id desc will only sort downwards, he wants to show only the last days of each month.

  • You can use queries SQL manually in theFileMaker? Por exemploSELECT * FROM CONTACTS`OR Script only??

  • can run query sql

5 answers

2

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.

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
  • When I started the answer you hadn’t specified the database yet, but I’ll keep it in case someone else needs it.

2

Another way to perform this query, only with calculations:

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

See working: Sqlfiddle

0


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

0

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))

  • Then in the FileMaker can only be used script ?

  • via sql does not give?

Browser other questions tagged

You are not signed in. Login or sign up in order to post.