比较存储为varchar的日期 [英] Comparing dates stored as varchar

问题描述

我需要将存储在我的数据库中的日期作为varchar与今天的日期进行比较。

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

我尝试过：

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

此选择的值，如17/06 / 2012 和19/04/2015，都已经通过，01/06/2015没有。

然后我尝试投入数据with：

  SELECT * 
 FROM tblServiceUsersSchedule 
 WHERE CAST（ScheduleEndDate as DATETIME） CAST（'05/31/2015'DATETIME）AND ScheduleEndDate！=''AND ScheduleEndDate不为空
  

但是得到以下错误：

将varchar数据类型转换为datetime数据类型导致
在一个out -f范围值。

我检查了后面的数据，没有空值，没有空白的空格。所有的日期格式为dd / mm / yyyy。

我不知道如何比较与今天日期存储的varchar日期。

解决方案

将日期值存储为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 将仅包含空格的值转换为空值。


4. 删除并重新创建引用这些列的索引。最简单的方法是右键单击SSMS上的索引，然后选择脚本索引为 - > drop并创建。


5. 使用 alter table 删除旧列。

注意：如果这些列在数据库上的任何其他对象中被引用，您也必须更改这些对象。这包括存储过程，外键等。

如果不能更改列的数据类型和您的sql server版本要低于2012年，您需要使用这样的转换：

  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 version 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（）作为Date）删除当前日期的时间部分。这意味着您只能收到 ScheduleEndDate 至少有一天的记录。如果您还想获得今天的 ScheduleEndDate 的记录，请使用< = 而不是 。
 
 
 
 最后一件事：使用where子句中的列使用函数将阻止Sql Server在这些列上使用任何索引。
 
这是您将列更改为适当数据类型的另一个原因。
 
I need to compare dates that are stored in my database as varchar against today's date.

Specifically,  I need to exclude any records with a date that has passed.

I tried:
SELECT * FROM tblServiceUsersSchedule 
WHERE ScheduleEndDate !='' AND ScheduleEndDate < '2015/05/31'
This selected values such as 17/06/2012 and 19/04/2015, which have both already passed, along with 01/06/2015 which hasn't.

I then tried to cast the data with:
SELECT * 
FROM   tblServiceUsersSchedule 
WHERE  CAST(ScheduleEndDate as DATETIME)  < CAST('05/31/2015' as DATETIME) AND ScheduleEndDate !='' AND ScheduleEndDate is not null
But got the following error:

  
The coversion of a varchar data type to a datetime data type resulted
  in an out-of-range value.
I checked the data behind and none are null, none are blank white space. All are dates in the format of dd/mm/yyyy.

I can't figure out how to compare the varchar date stored with todays date.
解决方案
Storing date values as varchar is simply wrong.  

If possible, you should alter the table to store them as date data type.

You can do it in a few simple steps:

Rename the current columns (I'm guessing ScheduleStartDate is also varchar) to columnName_old. This can be easily done by using sp_rename.
Use alter table to add the columns with the appropriate data type. 
Copy the values from the old columns to the new columns using an update statement. Since all of the dates are stored in the same format, you can use convert like this:  set ScheduleStartDate = convert(date, NULLIF(ltrim(rtrim(ScheduleStartDate_old)), ''), 103) If your sql server version is 2012 or higher, use try_convert. Note i've used the nullif, ltrim and rtrim to convert values that only contains white spaces to null.
Drop and recreate indexes that is referencing these columns. The simplest way to do this is by right-clicking the index on SSMS and choose script index as -> drop and create.
Use alter table to remove the old columns.
Note: if these columns are being referenced in any other objects on the database you will have to change these objects as well. This includes stored procedures, foreign keys etc`.

If you can't change the data types of the columns, and your sql server version is lower then 2012, you need to use convert like this:
SELECT * FROM tblServiceUsersSchedule 
WHERE CONVERT(DATE, NULLIF(ScheduleEndDate, RTRIM(LTRIM('')), 103) 
      < CAST(GETDATE() As Date);
AND ScheduleEndDate IS NOT NULL
Note that if you have even a single row where the column's data is not in dd/MM/yyyy format this will raise an error.

For sql server versions 2012 or higher, use Try_convert. This function will simply return null if the conversion fails:
SELECT * FROM tblServiceUsersSchedule 
WHERE TRY_CONVERT(DATE, NULLIF(ScheduleEndDate, RTRIM(LTRIM('')), 103)
      < CAST(GETDATE() As Date);
AND ScheduleEndDate IS NOT NULL
Note: I've used CAST(GETDATE() as Date) to remove the time part of the current date. This means that you will only get records where the ScheduleEndDate is at least one day old. If you want to also get the records where the ScheduleEndDate is today, use <= instead of <.

One final thing: Using functions on columns in the where clause will prevent Sql Server to use any indexing on these columns.

This is yet another reason why you should change your columns to the appropriate data type.

                        
这篇关于比较存储为varchar的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！