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.
我有一个表,其中包含多个年中每个月的多个记录。有人可以帮我写一个只返回每个月最后一天的查询。
8 个解决方案
#1
12
SQL Server (other DBMS will work the same or very similarly):
SQL Server(其他DBMS将以相同或非常相似的方式工作):
SELECT
*
FROM
YourTable
WHERE
DateField IN (
SELECT MAX(DateField)
FROM YourTable
GROUP BY MONTH(DateField), YEAR(DateField)
)
An index on DateField is helpful here.
DateField上的索引在这里很有用。
PS: If your DateField contains time values, the above will give you the very last record of every month, not the last day's worth of 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包含时间值,上面的内容将为您提供每月的最后一条记录,而不是最后一天的记录。在这种情况下,使用方法在进行比较之前将日期时间减少到其日期值,例如这个。
#2
3
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 that day 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是该月的最后一天的记录。
#3
2
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.
剥离/标准化外来时间组件留给读者练习。
#4
0
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)
#5
0
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
#6
0
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
#7
0
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
#8
-1
A simple way to get the last day of month is to get the first day of the next month and subtract 1.
获取月份最后一天的简单方法是获取下个月的第一天并减去1。
#1
12
SQL Server (other DBMS will work the same or very similarly):
SQL Server(其他DBMS将以相同或非常相似的方式工作):
SELECT
*
FROM
YourTable
WHERE
DateField IN (
SELECT MAX(DateField)
FROM YourTable
GROUP BY MONTH(DateField), YEAR(DateField)
)
An index on DateField is helpful here.
DateField上的索引在这里很有用。
PS: If your DateField contains time values, the above will give you the very last record of every month, not the last day's worth of 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包含时间值,上面的内容将为您提供每月的最后一条记录,而不是最后一天的记录。在这种情况下,使用方法在进行比较之前将日期时间减少到其日期值,例如这个。
#2
3
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 that day 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是该月的最后一天的记录。
#3
2
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.
剥离/标准化外来时间组件留给读者练习。
#4
0
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)
#5
0
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
#6
0
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
#7
0
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
#8
-1
A simple way to get the last day of month is to get the first day of the next month and subtract 1.
获取月份最后一天的简单方法是获取下个月的第一天并减去1。