从 sql select *EDITED* 的 where 子句中的子字符串获取日期

Question

我有一个日期与其他文本一起存储在文本字段中。他们为什么不把它放在日期字段中？我不知道，但我现在没有能力改变它。在代码的其他地方，我这样做是为了获取该日期在特定范围内的记录。效果很好。

For Each i As InventoryItem In inventoryList
  index = i.Notes.IndexOf("on {") + 4
  scanned_dt = i.Notes.Substring(index, i.Notes.Length - index - 1)
  If Date.Parse(scanned_dt) >= startDate And Date.Parse(scanned_dt) <= endDate Then
    ...

我现在正在尝试获取特定日期范围内的项目总数。 此 sql 语句用于获取所有日期的总计。如何更新Where子句以仅计算i.Notes包含startDate和endDate之间日期的项目

Dim sql As String = "Select COUNT(inv_PartNum) from lester.inventory i join lester.vendor v on v.vendor_ID = i.vendor_ID Where v.vendor_Name = '" + vendorName + "' AND i.inv_Desc LIKE '%" + size + "%'"

*已编辑* 我想出了这个 sql select 语句：

SELECT COUNT(i.inv_PartNum) FROM cdms.lester.inventory AS I
join CDMS.lester.vendor AS v on v.vendor_ID = i.vendor_ID Where v.vendor_Name = 'JVE-285' 
AND CONVERT(DATETIME,
SUBSTRING(i.inv_Notes, CharIndex('on {', i.inv_Notes)+4, len(i.inv_Notes)-(CharIndex('on {', i.inv_Notes) + 4)
),101) BETWEEN '01-01-2011' AND '04-04-2011'

但我收到此错误： 将 char 数据类型转换为 datetime 数据类型导致日期时间值超出范围。

在尝试解决这个问题时，我创建了以下 sql select 语句：

SELECT * FROM (
SELECT i.inv_Notes, 
CONVERT(DATETIME,SUBSTRING(i.inv_Notes, CharIndex('on {',i.inv_Notes)+4,LEN(i.inv_Notes)-(CharIndex('on {', i.inv_Notes) + 4)),101) AS d
FROM cdms.lester.inventory AS i
join CDMS.lester.vendor AS v ON v.vendor_ID = i.vendor_ID WHERE v.vendor_Name = 'JVE-285') AS s

我的 inv_Notes 列包含一个字符串例如“于 {4/8/2011} 分配给工具预告片 {JVE-285}”

当我运行如上所示的查询时，我会得到 inv_Notes 列以及日期列。日期均以“2011-04-08 00:00:00.000”格式显示，并且不会引发任何错误。

但是，一旦我添加 WHERE 子句，就会收到错误：将 char 数据类型转换为日期时间数据类型导致日期时间值超出范围。

我尝试过格式化各种方式的日期，但总是收到错误...

WHERE s.d > CONVERT(DATETIME, '2011-1-1', 101)

WHERE s.d < GetDate()

WHERE s.d > '20110101'

WHERE s.d >= '2011-01-01'

WHERE s.d >= '01-01-2011'

WHERE s.d > '01/01/2011'

编辑 我也尝试过 其中 sd 不为空 并得到相同的错误。这显然不是按照我认为的方式工作的，在 WHERE 点，转换应该已经成功发生。

解决方案 得到这个工作后，

SELECT * FROM (
SELECT i.inv_Notes,
SUBSTRING(i.inv_Notes, CharIndex('} on {',i.inv_Notes)+6,LEN(i.inv_Notes)-(CharIndex('} on {', i.inv_Notes) + 6))
AS d
FROM cdms.lester.inventory AS i
join CDMS.lester.vendor AS v ON v.vendor_ID = i.vendor_ID WHERE v.vendor_Name = 'JVE-285') AS s
WHERE PARSENAME(REPLACE(s.d, '/', '.'), 1)+
RIGHT('00'+PARSENAME(REPLACE(s.d, '/', '.'), 3),2)+
RIGHT('00'+PARSENAME(REPLACE(s.d, '/', '.'),2),2) BETWEEN '20110407' AND '20110409'

我尝试将其转换为 int 并进行整数比较，但随后出现错误。发现问题在于某些 inv_Notes 字段包含诸如“Item Updated {3/11/2011}”之类的数据，这些数据都不符合内部选择的条件，所以在我看来，如果未在内部选择中选择它，对于外部选择的条件来说，这应该不是问题。但是，它试图将 2011{311 转换为 int 并引发错误。我确信它正在尝试将其投射到迄今为止，这就是我遇到之前问题的原因。

Answer 1

尝试在语句中添加类似于此 WHERE 子句的内容：

WHERE CAST(SUBSTRING(Notes, CHARINDEX(Notes, 'on {') + 4, Length?) AS Date) BETWEEN StartDate And EndDate

想法是通过组合 SubString 和 CharIndex 方法提取字符串的相关部分，然后将此表达式转换为日期格式，以便可以与 Between 一起使用操作员。

祝你好运

第 2 部分更新：

由于您已经能够选择日期，但不能在 where 子句中使用它，我建议将其用作 select 语句，然后将其包装在另一个语句中，例如：

SELECT *
FROM
(SELECT CAST(SUBSTRING(Notes, CHARINDEX(Notes, 'on {') + 4, Length?) AS Date) AS dtmNotes)
WHERE dtmNotes BETWEEN Start And End

这只是为了说明，但包括包装中的整个第一个 select 语句。

Answer 2

试试这个...

假设带有嵌入日期的表称为 InventoryList 并且该列称为 inv_Notes

SELECT *
FROM InventoryList i
WHERE

CAST(
substring(
i.inv_Notes,
patindex('%on {%',i.inv_Notes)  +4,
patindex('%[0-9][0-9][0-9][0-9]}%',i.inv_Notes)-patindex('%on {%',i.inv_Notes)
) as Datetime)

BETWEEN '12/1/2011' AND '12/23/2011

编辑：更严格地查找“9/9999” }" 模式

SELECT *
FROM InventoryList i
WHERE

CAST(
substring(
i.inv_Notes,
patindex('%on {%',i.inv_Notes)  +4,
patindex('%[0-9]/[0-9][0-9][0-9][0-9]}%',i.inv_Notes)-patindex('%on {%',i.inv_Notes)
) as Datetime)

BETWEEN '12/1/2011' AND '12/23/2011