使用 SQL 仅返回每个月的最后一天
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5866054/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Return just the last day of each month with SQL
提问by Matt
I have a table that contains multiple records for each day of the month, over a number of years. Can someone help me out in writing a query that will only return the last day of each month.
我有一个表格，其中包含数年来每月每一天的多条记录。有人可以帮我写一个只返回每个月最后一天的查询。
回答by Tomalak
SQL Server (other DBMS will work the same or verysimilarly):
SQL Server（其他 DBMS 的工作方式相同或非常相似）：
SELECT
*
FROM
YourTable
WHERE
DateField IN (
SELECT MAX(DateField)
FROM YourTable
GROUP BY MONTH(DateField), YEAR(DateField)
)
An index on DateFieldis helpful here.
索引在DateField这里很有帮助。
PS: If your DateFieldcontains time values, the above will give you the very last recordof every month, not the last day's worthof records. In this case use a method to reduce a datetime to its date value before doing the comparison, for example this one.
PS：如果你DateField包含时间值，上面会给你每个月的最后一个记录，而不是最后一天的记录。在这种情况下，在进行比较之前使用一种方法将日期时间减少为其日期值，例如this one。
回答by Javier Rapoport
The easiest way I could find to identify if a date field in the table is the end of the month, is simply adding one day and checking if thatday is 1.
我能找到的最简单的方法来确定表中的日期字段是否是月末，只需添加一天并检查该天是否为 1。
where DAY(DATEADD(day, 1, AsOfDate)) = 1
If you use that as your condition (assuming AsOfDate is the date field you are looking for), then it will only returns records where AsOfDate is the last day of the month.
如果您使用它作为您的条件（假设 AsOfDate 是您要查找的日期字段），那么它只会返回 AsOfDate 是该月最后一天的记录。
回答by Nicholas Carey
In SQL Server, this is how I usually get to the last day of the month relative to an arbitrary point in time:
在 SQL Server 中，这就是我通常如何到达相对于任意时间点的一个月的最后一天：
select dateadd(day,-day(dateadd(month,1,current_timestamp)) , dateadd(month,1,current_timestamp) )
In a nutshell:
简而言之：
- From your reference point-in-time,
- Add 1 month,
- Then, from the resulting value, subtract its day-of-the-month in days.
- 从您的参考时间点，
- 加1个月，
- 然后，从结果值中减去其月中的天数。
Voila! You've the the last day of the month containing your reference point in time.
瞧！你有一个月的最后一天包含你的参考时间点。
Getting the 1st day of the month is simpler:
获取一个月的第一天更简单：
select dateadd(day,-(day(current_timestamp)-1),current_timestamp)
- From your reference point-in-time,
- subtract (in days), 1 less than the current day-of-the-month component.
- 从您的参考时间点，
- 减去（以天为单位），比当前月份中的某一天少 1。
Stripping off/normalizing the extraneous time component is left as an exercise for the reader.
剥离/标准化无关的时间分量留给读者作为练习。
回答by FistOfFury
Use the EOMONTH() function if it's available to you (E.g. SQL Server). It returns the last date in a month given a date.
如果您可以使用 EOMONTH() 函数（例如 SQL Server），请使用它。它返回给定日期的一个月中的最后一个日期。
select distinct
Date
from DateTable
Where Date = EOMONTH(Date)
Or, you can use some date math.
或者，您可以使用一些日期数学。
select distinct
Date
from DateTable
where Date = DATEADD(MONTH, DATEDIFF(MONTH, -1, Date)-1, -1)
回答by Olumide Ibilaiye
This should work on Oracle DB
这应该适用于 Oracle DB
select distinct last_day(trunc(sysdate - rownum)) dt
from dual
connect by rownum < 430
order by 1
回答by Amit
I did the following and it worked out great. I also wanted the Maximum Date for the Current Month. Here is what I my output is. Notice the last date for July which is 24th. I pulled it on 7/24/2017, hence the result
我做了以下工作，效果很好。我还想要当月的最大日期。这是我的输出。请注意 7 月的最后日期，即 24 日。我在 2017 年 7 月 24 日拉了它，因此结果
Year Month KPI_Date
2017 4 2017-04-28
2017 5 2017-05-31
2017 6 2017-06-30
2017 7 2017-07-24
SELECT B.Year ,
B.Month ,
MAX(DateField) KPI_Date
FROM Table A
INNER JOIN ( SELECT DISTINCT
YEAR(EOMONTH(DateField)) year ,
MONTH(EOMONTH(DateField)) month
FROM Table
) B ON YEAR(A.DateField) = B.year
AND MONTH(A.DateField) = B.Month
GROUP BY B.Year ,
B.Month
回答by Shekhar Nalawade
SELECT * FROM YourTableName WHERE anyfilter
AND "DATE" IN (SELECT MAX(NameofDATE_Column) FROM YourTableName WHERE
anyfilter GROUP BY
TO_CHAR(NameofDATE_Column,'MONTH'),TO_CHAR(NameofDATE_Column,'YYYY'));
Note: this answer does apply for Oracle DB
注意：此答案适用于 Oracle DB
回答by jw11432
Here's how I just solved this. day_dateis the date field, calendaris the table that holds the dates.
这是我刚刚解决这个问题的方法。day_date是日期字段，calendar是保存日期的表。
SELECT cast(datepart(year, day_date) AS VARCHAR)
+ '-'
+ cast(datepart(month, day_date) AS VARCHAR)
+ '-'
+ cast(max(DATEPART(day, day_date)) AS VARCHAR) 'DATE'
FROM calendar
GROUP BY datepart(year, day_date)
,datepart(month, day_date)
ORDER BY 1
回答by Aaron Digulla
A simple way to get the last day of month is to get the first day of the next monthand subtract 1.
一个简单的方法来获得一个月的最后一天拿到的第一天下个月再减去1。
