英文:
Get the Number of the Month in SQL between to Dates
问题
获取SQL中两个日期之间的月份数方法：
示例：在2023-01-04和2023-05-04之间（输出01、02、03、04、05）对应月份的数量
如果在08和10之间（08、09、10）
不考虑日期
英文:
How do we Get the Number of the Month in SQL between to Dates
Example: between 2023-01-04 and 2023-05-04 (output 01 , 02,03,04,05)number of the coresponding months
if between 08 and 10 (08,09,10)
Irrespective of the date
答案1
得分: 1
以下是您提供的代码的翻译：
declare @start DATE = '2023-01-04'
declare @end DATE = '2023-05-04'
;with months (date)
AS
(
SELECT @start
UNION ALL
SELECT DATEADD(month, 1, date)
from months
where DATEADD(month, 1, date) < @end
)
select [LastDayOfMonth] = EOMONTH(date)
from months
这将为您生成以下输出：
| LastDayOfMonth |
|---|
| 2023-01-31 |
| 2023-02-28 |
| 2023-03-31 |
| 2023-04-30 |
查看：DBFIDDLE
如果您只想获取它们之间的差异（如 "1"），我会使用DATEDIFF函数。
英文:
I would write it as follows:
declare @start DATE = '2023-01-04'
declare @end DATE = '2023-05-04'
;with months (date)
AS
(
SELECT @start
UNION ALL
SELECT DATEADD(month, 1, date)
from months
where DATEADD(month, 1, date) < @end
)
select [LastDayOfMonth] = EOMONTH(date)
from months
This would give you the following output:
| LastDayOfMonth |
|---|
| 2023-01-31 |
| 2023-02-28 |
| 2023-03-31 |
| 2023-04-30 |
see: DBFIDDLE
If you only want to have the difference in between (Like "1") I would use the DATEDIFF function.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论