查询用于搜索以字符串形式存储在数据库中的日期

Question

我有一个表，我存储一个活动完成日期为varchar。存储的日期格式为MM/DD/YYYY HH:MM:SS。我有一个搜索窗口，我有两个字段Completion date from和completion date to .这里选择的日期格式是MM/DD/YYYY。

我如何编写一个查询，使我能够从具有日期存储为varchar的表中获取两个给定日期之间的活动完成。这个表是很久以前创建的，没有考虑过将日期保存为datetime。

Answer 1

您可以使用SQL CONVERT将列更改为DATE格式，但这会导致性能问题。

SELECT *
    FROM MyTable
    WHERE CONVERT(DATETIME, MyDate) >= CONVERT(DATE, '01/01/2014')
        AND CONVERT(DATETIME, MyDate) <= CONVERT(DATE, '01/31/2014') 

CONVERT文档- http://msdn.microsoft.com/en-us/library/ms187928.aspx

如果您无法更改数据的存储方式，那么为了获得更好的性能，您可以创建具有计算列的视图，将VARCHAR转换为DATETIME。之后可以在计算列上创建索引。计算列文档索引

Answer 2

使用SUBSTRING函数以可比较的顺序(即yyyymmdd)获取日期部分:

select *
from mytable 
where 
  CONCAT( SUBSTRING(thedate, 7, 4) , SUBSTRING(thedate, 4, 2) , SUBSTRING(thedate, 1, 2) )
between
  CONCAT( SUBSTRING(@FROMDATE, 7, 4) , SUBSTRING(@FROMDATE, 4, 2) , SUBSTRING(@FROMDATE, 1, 2) )
and
  CONCAT( SUBSTRING(@TODATE, 7, 4) , SUBSTRING(@TODATE, 4, 2) , SUBSTRING(@TODATE, 1, 2) )
;

Answer 3

您可以使用以下代码:

 select * from table_name
 where  CAST(col1 as date )  
between CAST(Completion date from as date ) 
        and CAST(Completion date to as date);

函数语法CAST:

CAST ( expression AS data_type )

Answer 4

如果日期格式为{yyyy-MM-dd}，则可以使用below，或者您可以根据格式调整charindex的索引值

SELECT * 
FROM table
WHERE 
    CHARINDEX('-', col_value, 0) = 5 
    AND CHARINDEX('-', col_value, 6) = 8
    AND LEN(col_value) = 10

上面的代码将查找第一个字符'-'出现在位置5，第二个字符'-'出现在位置8，而整个日期值的长度等于10个字符

这是不是完全证明，但将缩小搜索范围。如果您想添加时间，那么只需在其中扩展标准以适应格式，即{yyyy-MM-dd 00:00:00.000}

这是一种安全的方式来查询数据，没有任何意外的'invalid cast/convert'错误。