动态计算已经过了多少个月 - SQL Server

I am trying to calculate how many months ago the date field was

我想计算几个月前的日期字段

I have a table

我有一张桌子

CREATE TABLE Date(
Date Date
);

INSERT INTO Date (Date)
VALUES ('05-01-18'),
('04-01-18'),
('03-01-18'),
('02-01-18'),
('01-01-18'),
('12-01-17'),
('11-01-17');

And a query

和一个查询

SELECT Date ,
MONTH(Date),
CASE WHEN MONTH(Date) = MONTH(GETDATE()) Then 'Current Month'
WHEN MONTH(Date) = MONTH(GETDATE()) -1 Then '1 Month Ago'
WHEN MONTH(Date) = MONTH(GETDATE()) -2 Then '2 Month Ago'
ELSE 'n/a' END AS [Months Ago]
FROM Date

Which gives me the correct result:

这给了我正确的结果：

|       Date |    |    Months Ago |
|------------|----|---------------|
| 2018-05-01 |  5 | Current Month |
| 2018-04-01 |  4 |   1 Month Ago |
| 2018-03-01 |  3 |   2 Month Ago |
| 2018-02-01 |  2 |           n/a |
| 2018-01-01 |  1 |           n/a |
| 2017-12-01 | 12 |           n/a |
| 2017-11-01 | 11 |           n/a |

But is there anyway to create this dynamically instead of keep having to write case expressions. So if anyone add's more dates in the future this will just work without having to add more cases?

但无论如何都要动态创建它，而不是继续编写case表达式。因此，如果有人在未来添加更多日期，这将无需添加更多案例即可运作？

2 个解决方案

#1

You exactly want datediff():

你确实想要datediff（）：

select datediff(month, date, getdate()) as num_months_ago

datediff() counts the number of month boundaries between two dates. So, Dec 31 is "one month before" Jan 1. This appears to be the behavior that you want.

datediff（）计算两个日期之间的月份边界数。所以，12月31日是“1月1日前一个月”。这似乎是你想要的行为。

I don't see an advantage to putting this in a string format.

我认为把它放在字符串格式中没有优势。

#2

In case you do want this to have a string format:

如果您确实希望它具有字符串格式：

SELECT D.[Date],
       DATEPART(MONTH,D.[Date]) AS [Month],
       CASE WHEN V.DD = 0 THEN 'Current Month'
            WHEN V.DD = 1 THEN  '1 Month Ago'
            ELSE CONVERT(varchar(4), V.DD) + ' Months ago' END AS MonthsAgo
FROM [Date] D
     CROSS APPLY (VALUES(DATEDIFF(MONTH, D.[Date], GETDATE()))) V(DD);

I, however, agree with Gordon, SQL Server isn't really the palce to do that type of formatting. :)

但是，我同意Gordon的观点，SQL Server并不是真正的格式化。 :)

#1