SQL:如何从批量插入表中从文件中提取正确的日期值并获得总金额

Question

我在表中插入了一个批量插入。

所有数据都插入Data列

我在数据列内有类似的东西：

1451678889   1451678889   2017092   2017092       500,000.00       10,000.00
1451678889   1451678889   2017091   2017091       500,000.00       10,000.00
1451678889   1451678889   2017092   2017092       500,000.00       10,000.00

现在，我需要以yyyy-mm-dd格式从2017092获得一个日期：其中2017年 - 年份和92岁是今年的许多一天。

另外，我需要按日期进行分组。

执行此查询时：

select substring([data],34,15) from Staging_Table where ltrim(rtrim(substring([Data],42,5))) = '92'

我得到正确的字符串2017092

试图测试逻辑时：

select dateadd(day, ltrim(rtrim(right('2017092',3))) - 1, DATEFROMPARTS(left('2017092',4) + 0, 1 ,1))

我明白了：2017-04-02，这是2017年的正确日期和第92天

现在，将" 2017092"替换为表中的实际数据时：

select dateadd(day,ltrim(rtrim(right(substring('1451678889   1451678889   2017092   2017092',34,15),3))) - 1, DATEFROMPARTS(left(substring('1451678889   1451678889   2017092   2017092',34,15),4) + 0, 1,1))

或查询实际表：

select dateadd(day,right(ltrim(rtrim(substring([Data],34,15))),3) - 1, DATEFROMPARTS(left(substring([Data],34,15),4) + 0, 1, 1)) from Staging_Table
where ltrim(rtrim(substring([Data],42,5))) = '92'

我会得到一些奇怪的结果：0002-04-02-犯错的年份。

我在做什么错？

Answer 1

将子字符串值调整为：

select 
    Days = substring('1451678889   1451678889   2017092   2017092',41,3)
  , Year = substring('1451678889   1451678889   2017092   2017092',37,4)
  , Date = dateadd(day,substring('1451678889   1451678889   2017092   2017092',41,3) - 1
    , datefromparts(substring('1451678889   1451678889   2017092   2017092',37,4)+0, 1,1)
  )

rextester演示：http：//rextester.com/med20206

返回：

+------+------+---------------------+
| Days | Year |        Date         |
+------+------+---------------------+
|  092 | 2017 | 2017-04-02 00:00:00 |
+------+------+---------------------+

---

要使它更加灵活，您可以使用patindex()来识别所需的子字符串的可能位置：

create table t (data varchar(256));
insert into t values 
 ('1451678889   1451678889   2017092   2017092       500,000.00       10,000.00')
,('1451678889   1451678889   2017091   2017091       500,000.00       10,000.00')
,('1451678889   1451678889   2017092   2017092       500,000.00       10,000.00');
/* --------------- */
select 
    Days = substring(data,patindex('% [1-2][0-9][0-9][0-9][0-9][0-9][0-9] %',data)+5,3)
  , Year = substring(data,patindex('% [1-2][0-9][0-9][0-9][0-9][0-9][0-9] %',data)+1,4)
  , Date = dateadd(day,substring(data,patindex('% [1-2][0-9][0-9][0-9][0-9][0-9][0-9] %',data)+5,3) - 1
    , datefromparts(substring(data,patindex('% [1-2][0-9][0-9][0-9][0-9][0-9][0-9] %',data)+1,4)+0, 1,1)
  )
from t

rextester演示：http：//rextester.com/jwjve60941

返回：

+------+------+---------------------+
| Days | Year |        Date         |
+------+------+---------------------+
|  092 | 2017 | 2017-04-02 00:00:00 |
|  091 | 2017 | 2017-04-01 00:00:00 |
|  092 | 2017 | 2017-04-02 00:00:00 |
+------+------+---------------------+

和稍微清理代码，我们可以将模式扔进变量：

declare @pattern varchar(64) = '% [1-2][0-9][0-9][0-9][0-9][0-9][0-9] %';
select 
    Days = substring(data,patindex(@pattern,data)+5,3)
  , Year = substring(data,patindex(@pattern,data)+1,4)
  , Date = dateadd(day,substring(data,patindex(@pattern,data)+5,3) - 1
    , datefromparts(substring(data,patindex(@pattern,data)+1,4)+0, 1,1)
  )
from t