比较以varchar形式存储的日期

21 浏览
0 Comments

比较以varchar形式存储的日期

我需要将数据库中以varchar类型存储的日期与今天的日期进行比较。

具体来说，我需要排除任何已过期的日期记录。

我尝试了以下方法：

SELECT * FROM tblServiceUsersSchedule 
WHERE ScheduleEndDate !='' AND ScheduleEndDate < '2015/05/31'

这个查询选取了已经过期的值，比如17/06/2012和19/04/2015，还有尚未过期的01/06/2015。

然后我尝试使用以下方法对数据进行转换：

SELECT * 
FROM   tblServiceUsersSchedule 
WHERE  CAST(ScheduleEndDate as DATETIME)  < CAST('05/31/2015' as DATETIME) AND ScheduleEndDate !='' AND ScheduleEndDate is not null

但是出现了以下错误：

将varchar数据类型转换为datetime数据类型时导致了一个超出范围的值。

我检查了数据，没有null值，也没有空白字符。所有日期都是以dd/mm/yyyy的格式。

我无法弄清如何将varchar类型的日期与今天的日期进行比较。

0

3 答案

0 Comments

比较存储为varchar的日期

在上述内容中，问题的出现原因是将日期存储为varchar类型。建议将日期存储为datetime类型的列，而不是varchar类型的列。解决方法是更改WHERE条件，以获取尚未过去的所有日期。查询语句应该如下：

SELECT * FROM tblServiceUsersSchedule

WHERE ScheduleEndDate IS NOT NULL

AND ScheduleEndDate >= '2015/05/31'

0
0 Comments

将日期值存储为varchar类型是错误的。

如果可能的话，你应该修改表格将它们存储为日期数据类型。

你可以通过以下几个简单的步骤来完成：

  1. 将当前列（我猜想ScheduleStartDate也是varchar类型）重命名为columnName_old。可以使用sp_rename来轻松完成此操作。
  2. 使用alter table添加具有适当数据类型的列。
  3. 使用update语句将旧列的值复制到新列。由于所有的日期都以相同的格式存储，可以使用convert，例如：set ScheduleStartDate = convert(date, NULLIF(ltrim(rtrim(ScheduleStartDate_old)), ''), 103)。如果你的sql server版本是2012或更高版本，请使用try_convert。注意我使用了nullif、ltrim和rtrim将只包含空格的值转换为null。
  4. 删除并重新创建引用这些列的索引。最简单的方法是在SSMS上右键点击索引，选择script index as -> drop and create。
  5. 使用alter table删除旧列。

注意：如果这些列在数据库中的其他对象中被引用，你也需要相应更改这些对象。这包括存储过程、外键等。

如果无法更改列的数据类型，并且你的sql server版本低于2012，你需要像这样使用convert：

SELECT * FROM tblServiceUsersSchedule 
WHERE CONVERT(DATE, NULLIF(ScheduleEndDate, RTRIM(LTRIM('')), 103) 
      < CAST(GETDATE() As Date);
AND ScheduleEndDate IS NOT NULL

请注意，如果有一行的列数据不是dd/MM/yyyy格式，这将引发错误。

对于sql server 2012或更高版本，请使用Try_convert。该函数如果转换失败将返回null：

SELECT * FROM tblServiceUsersSchedule 
WHERE TRY_CONVERT(DATE, NULLIF(ScheduleEndDate, RTRIM(LTRIM('')), 103)
      < CAST(GETDATE() As Date);
AND ScheduleEndDate IS NOT NULL

注意：我使用CAST(GETDATE() as Date)来去除当前日期的时间部分。这意味着你只会得到ScheduleEndDate至少一天前的记录。如果你还想获取ScheduleEndDate是今天的记录，使用<=而不是<。

最后一点：在where子句中对列使用函数会阻止Sql Server使用这些列上的任何索引。这是为什么你应该将列更改为适当的数据类型的另一个原因。

0
0 Comments

如果你必须将日期存储为varchar（正如其他答案所述，这是一种不好的做法），那么使用ISO格式（例如yyyy-mm-dd）可以进行文本比较而无需任何问题。

如果你的列是日期数据类型，并且你使用的是SQL 2012或更高版本，则可以使用DATEFROMPARTS（或其变体之一）进行日期比较，例如：

WHERE DateToCompare < DATEFROMPARTS (2019, 12, 31)

而不是

WHERE DateToCompare < '2019-12-31'

SQL可以处理后者，但前者更加“正确”。

比较日期时，将日期存储为varchar可能会导致一些问题。varchar类型的数据不能直接进行日期比较，因为它们是按照字母顺序进行比较的，而不是按照日期顺序进行比较。这可能会导致不正确的比较结果。

为了解决这个问题，可以使用ISO格式（例如yyyy-mm-dd）存储日期作为varchar类型。这样可以确保文本比较时按照日期顺序进行比较，而不会产生错误的结果。

另外，如果你的列是日期数据类型，并且使用的是SQL 2012或更高版本，可以使用DATEFROMPARTS函数进行日期比较。这个函数可以根据年份、月份和日期构建一个日期，然后可以将其用于比较。使用DATEFROMPARTS函数进行日期比较比直接使用varchar类型的日期更加准确。

总之，将日期存储为varchar类型可能会导致比较问题，但可以通过使用ISO格式存储日期并使用DATEFROMPARTS函数进行日期比较来解决这个问题。这样可以确保比较结果是正确的。

0