如何将 MMYYYY (042011) 转换为日期并查找今天日期的日期差异

Question

我有一个varchar列，其中包含042011 MMYYYY格式的数据。我想转换它以找到从今天日期到该列的差距。

我想要一个像 2.2 年或相关的东西这样的答案。

我试过了

SELECT datediff(month,FORMAT(GETDATE(),'MM') + FORMAT(GETDATE(),'yyyy'),'042011')

Answer 1

MMYYYY

不是日期，因为它不包含月日。我怀疑这是一个引用特定报告期的字符串。

处理此类期间的典型方法是使用日历表，例如 20 或 50 年的日期和年、月、月、周数、月份名称以及最重要的业务报告期间的额外字段。

此表使每个周期的聚合或比较不同周期变得更加容易和快捷。在各个列列上添加索引，以便根据年份等进行非常快速的连接和分组。

假设日历表看起来有点像这样：

create table Calendar
(
Date DATE NOT NULL PRIMARY KEY,
Year int NOT NULL
Month int not null,
....
MonthLabel char(6),
IX_Calendar_Year (Year),
....
IX_Calendar_MonthLabel (MonthLabel),
)

您可以通过以下方式计算例如每月的总和：

select Year,Month,SUM(Total) as Total
From Orders inner join Calendar 
on Calendar.Date=Orders.Date
group by Year, Month

使用报告期间同样简单：

select MonthLabel,SUM(Total) as Total
From Orders inner join Calendar 
on Calendar.Date=Orders.Date
group by MonthLabel

如果源数据包含报告期间标签，则可以联接该列：

select MonthLabel,SUM(Total) as Total
From Orders inner join Calendar 
on Calendar.MonthLabel=Orders.MonthLabel

如果标签来自UI，例如报告工具：

select Year,Month,SUM(Total) as Total
From Orders inner join Calendar 
on Calendar.Date=Orders.Date
WHERE MonthLabel=@thatLabel
group by Year, Month

所有这些查询都很快，因为它们不涉及解析，并且连接、分组、筛选操作使用索引列

Answer 2

你想要这样的东西吗？

SELECT ROUND(CAST(DATEDIFF (month,CAST(RIGHT ('042011',4) + '-' + LEFT ('042011',2) + '-' + '01' AS DATE),CAST(GETDATE () AS DATE)) AS FLOAT) / CAST(12 AS FLOAT),2)

结果：8.2

我将您的 varchar 列设置为 dateformat (2011-04-01) 我添加一个默认日期 (1)，然后将其月份时间差异设置为当前日期并将月份除以 12 以获得年数。还做了一些浮法铸造和四舍五入以获得所需的结果。

Answer 3

SELECT DATEDIFF(
month, 
GETDATE(), 
PARSE(
CONCAT(
RIGHT('042011', 4),'-',LEFT('042011', 2)) as date USING 'en-US'))

输出

-98

意味着 98 个月前

Answer 4

我知道代码很乱。但是你可以为它编写一个函数。

试试这个：

SELECT cast(datediff(year,CONVERT(DATETIME,'20110301',103),CONVERT(DATETIME,(FORMAT(GETDATE(),'yyyyMM')+'01'),103)) as varchar)+'.'+
cast((datediff(month,CONVERT(DATETIME,'20110301',103),CONVERT(DATETIME,(FORMAT(GETDATE(),'yyyyMM')+'01'),103)) -(datediff(year,CONVERT(DATETIME,'20110301',103),CONVERT(DATETIME,(FORMAT(GETDATE(),'yyyyMM')+'01'),103))*12)) as varchar)

或

如果你想通过功能来做到这一点，身体会看起来像这样：

Declare @currentdate;
Declare @datetocompare varchar(10);
Declare @years varchar(10);
Declare @months varchar(10);
set @datetocompare='20110401';
set @currentdate=GETDATE();
select @years=datediff(year,CONVERT(DATETIME,@datetocompare,103),@currentdate);
select @months=datediff(month,CONVERT(DATETIME,@datetocompare,103),@currentdate)-(datediff(year,CONVERT(DATETIME,@datetocompare,103),@currentdate)*12);
select @years  +'.' +@months +' Years' asYearMonthDay;

Answer 5

让我们逐步构建它，以便您可以遵循逻辑

declare @value varchar(6) = '042011'
declare @datestring varchar(8)
declare @date date
-- convert your format to a regional independant format including day 1
select @datestring = concat(right(@value, 4), left(@value, 2), '01')
-- convert this into a valid date type
select @date = convert(date, @datestring)
-- now we can get the difference, first the years and then the months
select  datediff(month, @date, getdate()) / 12 as years,
datediff(month, @date, getdate()) % 12 as months

总之，它看起来像这样

select datediff(month, convert(date, (concat(right(@value, 4), left(@value, 2), '01'))), getdate()) / 12 as years,
datediff(month, convert(date, (concat(right(@value, 4), left(@value, 2), '01'))), getdate()) % 12 as months

其中@value可以用表格的列替换

例如，这在查询中的外观

declare @table table (value varchar(8))
insert into @table (value) 
values ('012011'), ('022011'), ('032011'), ('042011'), ('052011'), ('062011'), ('072011'), ('082011'), ('092011'), ('102011'), ('112011'), ('122011')
select value,
datediff(month, convert(date, (concat(right(value, 4), left(value, 2), '01'))), getdate()) / 12 as years,
datediff(month, convert(date, (concat(right(value, 4), left(value, 2), '01'))), getdate()) % 12 as months
from   @table

结果是(getdate() returned 20190604 on the time of writing this)

value   years   months  
-----   -----   ------  
012011  8        5  
022011  8        4  
032011  8        3  
042011  8        2  
052011  8        1  
062011  8        0  
072011  7       11  
082011  7       10  
092011  7        9  
102011  7        8  
112011  7        7  
122011  7        6  

Answer 6

一种可能的方法是下一个语句。

输入：

CREATE TABLE #Dates (
MmYyyy varchar(6)
)
INSERT INTO #Dates 
(Mmyyyy)
VALUES
('042011'),
('052011'),
('062011')

T-SQL：

SELECT CONCAT(
DATEDIFF(month,TRY_CONVERT(date, CONCAT(RIGHT(MmYyyy, 4), LEFT(MmYyyy, 2), '01')), GETDATE()) / 12, 
' y, ',
DATEDIFF(month,TRY_CONVERT(date, CONCAT(RIGHT(MmYyyy, 4), LEFT(MmYyyy, 2), '01')), GETDATE()) % 12,
' m'
) AS [Difference]
FROM #Dates

输出：

Difference
8 y, 2 m
8 y, 1 m
8 y, 0 m

笔记：

关于您的问题中的"我想要 2.2 年或相关答案"。如果你想要正确的结果，计算应该是years and months，而不是years and months / 12。只需查看下一个示例脚本 - 3 个月和 4 个月以及 9 个月和 10 个月的差异相等。四舍五入到小数点后 1 位以上会使 resul 难以理解。

SELECT CONVERT(numeric(10, 1), v.Nmr / 12.0) AS [Difference]
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) v(Nmr)
Difference
0.1
0.2
0.3
0.3
0.4
0.5
0.6
0.7
0.8
0.8
0.9
1.0