SQL 2012 - 查询返回月份数 [英] SQL 2012 - Query to return month number

问题描述

我需要返回给定日期的月份数.当前月份的月份编号为 0.下个月的 MonthNo 为 1.上个月为 -1，2 个月前为 -2.等等因此假设今天是 2020 年 6 月 16 日，请参阅下面的示例数据:

I need to return the month number for a given date. For the current month, the month number would be 0. For the following month, the MonthNo would be 1. The previous month would be -1, 2 months ago would be -2. etc So assuming today is 16 June 2020, see example data below:

IF OBJECT_ID('tempdb..#DateTest') IS NOT NULL
    DROP TABLE #DateTest

CREATE TABLE #DateTest
(
DateTest Date
)

INSERT INTO #DateTest
VALUES
('23/08/2020'),
('14/06/2020'),
('01/06/2020'),
('15/05/2020'),
('01/05/2020'),
('18/01/2020') 

Select * FROM #DateTest

我需要返回以下内容:

Date          MonthNo
23/08/2020    2
14/06/2020    0
01/06/2020    0
15/05/2020    -1
01/05/2020    -1
18/01/2020    -5

有什么办法可以返回这些信息吗?

Is there any way to return this info?

推荐答案

看起来很普通 DATEDIFF，它应该适合你:

Looks like ordinary DATEDIFF, it should work for you:

SELECT 
  DateTest AS [Date],
  DATEDIFF(MONTH, GETDATE(), DateTest) AS [MonthNo]
FROM #DateTest

演示

这篇关于SQL 2012 - 查询返回月份数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！