SQL查询用于检索日期最后一天的记录 [英] SQL Query for retreiving records that fall on the last day of the month
问题描述
我想执行一个查询，只返回属于日期的最后一天的记录。如下：
SELECT * FROM mytable
WHERE DATEPART（d，mytable.rdate）= DATEPART（d，DATEADD （m，1，DATEADD（d，-1，mytable.rdate）））
作为参数右侧的 rdate 应该是 dateadd 返回正确的比较。
基本上有一个简洁的方法来做这个比较？
等同于四分之一也是非常有帮助的，但无疑更复杂。
编辑：
本质上，我想检查一个给定的日期是否属于任何一个月（或四分之一）的最后一个。如果是，请返回该记录。这将涉及一些功能，以返回任何日期的月份的最后一天。例如 lastdayofmonth（'2013-06-10'）= 30 （所以这个记录不会被返回。
EDIT2 ：
如果返回在季度最后一天的记录，那么它们将需要如下：
SELECT * FROM mytable
WHERE DATEPART（'d'，mytable.rdate）= lastdayofmonth（mytable.rdate）
AND DATEPART 'm'，mytable.rdate）= lastmonthofquarter（mytable.rdate）;
棘手的是 lastdayofmonth（）和 lastmonthofquarter（）函数
使用日期序列函数计算给定日期的最后一天。
将零作为第三个参数传递 ，实际上返回以前月份的最后一个日期。
rdate =＃2013-7-24＃
？ DateSerial（Year（rdate），Month（rdate），0）
6/30/2013
所以要从 rdate 月中获取最后一个日期，请将 1 添加到月份参数。
？ DateSerial（Year（rdate），Month（rdate）+ 1，0）
7/31/2013
您可能会怀疑这种方法会在十二月 rdate 中断，因为 Month（）+ 1 将返回然而， DateSerial 仍然处理它。
rdate =＃2013-12-1＃
？ DateSerial（Year（rdate），Month（rdate）+ 1，0）
12/31/2013
如果您将在Access应用程序会话中运行查询，则可以基于该方法构建VBA函数，并在查询中使用自定义函数。
但是，如果查询将从ODBC或OleDb连接运行到Access数据库，则查询不能使用VBA用户定义的函数。在这种情况下，您可以直接在查询中使用 DateSerial 。
SELECT m。*
FROM mytable AS m
WHERE m.rdate = DateSerial（Year（m.rdate），Month（m.rdate ）+ 1，0）
如果您的 rdate 值都包括午夜作为时间组件。如果这些值包括其他时间，请使用 DateValue 。
WHERE DateValue（m.rdate）= DateSerial（Year（m.rdate），Month（m.rdate）+ 1，0）
I have a table with records from many dates. I would like to perform a query that only returns records that fall on the last day of the date's month. Something like:
SELECT * FROM mytable
WHERE DATEPART(d, mytable.rdate) = DATEPART(d,DATEADD(m,1,DATEADD(d, -1, mytable.rdate)))
Except this doesn't work as the rdate in the right-hand side of the argument should be the last day of it's month for the dateadd to return the correct comparison.
Basically is there an concise way to do this comparison? An equivalent for quarter-ends would also be very helpful, but no doubt more complex.
EDIT:
Essentially I want to check if a given date is the last in whatever month (or quarter) it falls into. If it is, return that record. This would involve a some function to return the last day of the month of any date. e.g. lastdayofmonth('2013-06-10') = 30 (so this record would not be returned.
EDIT2:
For the case of returning the records that fall on the last day of the quarter they are in it would need to be something like:
SELECT * FROM mytable
WHERE DATEPART('d', mytable.rdate) = lastdayofmonth(mytable.rdate)
AND DATEPART('m', mytable.rdate) = lastmonthofquarter(mytable.rdate);
The tricky bit is the lastdayofmonth() and lastmonthofquarter() functions
Use the DateSerial Function to compute the last day of the month for a given date.
Passing zero as the third argument, day, actually returns the last date of the previous month.
rdate = #2013-7-24#
? DateSerial(Year(rdate), Month(rdate), 0)
6/30/2013
So to get the last date from the rdate month, add 1 to the month argument.
? DateSerial(Year(rdate), Month(rdate) + 1, 0)
7/31/2013
You might suspect that approach would break for a December rdate, since Month() + 1 would return 13. However, DateSerial still copes with it.
rdate = #2013-12-1#
? DateSerial(Year(rdate), Month(rdate) + 1, 0)
12/31/2013
If you will be running your query from within an Access application session, you can build a VBA function based on that approach, and use the custom function in the query.
However, if the query will be run from an ODBC or OleDb connection to the Access db, the query can not use a VBA user-defined function. In that situation, you can use DateSerial directly in your query.
SELECT m.*
FROM mytable AS m
WHERE m.rdate = DateSerial(Year(m.rdate), Month(m.rdate) + 1, 0)
That should work if your rdate values all include midnight as the time component. If those values include other times, use DateValue.
WHERE DateValue(m.rdate) = DateSerial(Year(m.rdate), Month(m.rdate) + 1, 0)
这篇关于SQL查询用于检索日期最后一天的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
