java - 将字符串转换为日期并比较日期

Question

我有一个数据库，其中日期列保存为 nvarchar 类型，日期格式为 dd/mm/yyyy(英国)。我正在尝试执行选择查询来搜索两个日期之间发布的数据。

查询是:

SELECT * FROM table 
  WHERE CONVERT(datetime,postdate,103) BETWEEN ? AND ?

我已将字段转换为日期时间，但每当我尝试运行查询时都会收到错误

java.lang.Exception: com.microsoft.sqlserver.jdbc.SQLServerException:
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value

当我运行 isDate 查询时:

SELECT * FROM table WHERE ISDATE(postdate)=0

显示了日期为 18/02/2013 的行。如果我没记错的话，这意味着 18 被视为一个月。

如何将我的字段转换为有效日期并比较日期？

Answer 1

第一个问题是您将日期存储在字符串列中。为什么不使用DATE或DATETIME？如果您这样做是为了保留 d/m/y 格式，请停止这样做。格式是表示层的职责，不应该影响存储。另外，为什么要使用 NVARCHAR？您预计日期文字中需要 Unicode 的符号类型是什么？

接下来是您没有适当的验证，并且错误的数据最终出现在您的列中。您可以按如下方式识别这些不良数据:

SET DATEFORMAT DMY;

SELECT * FROM dbo.table
  WHERE ISDATE(postdate) = 0;

现在，通过在知道数据是什么时更新数据或将这些行设置为 NULL 来修复该数据，直到查询返回 0 行。

请注意，您可能拥有诸如 11/13/2013 之类的数据，这显然不是 D/M/Y 格式的数据，如果是这种情况，您还可能拥有似乎<的数据/strong> 有效，但是输入 06/09/2013 的用户是指 6 月 9 日还是 9 月 6 日？你怎么能确定？

防止明天再次发生此问题的最安全方法可能是添加新列、移动数据、删除旧列，并停止在所有层使用字符串。 p>

ALTER TABLE dbo.table
 ADD d DATE;

UPDATE dbo.table 
 SET d = CONVERT(DATETIME, postdate, 103);

ALTER TABLE dbo.table
 DROP COLUMN postdate;

EXEC sp_rename N'dbo.table.d', N'postdate', N'COLUMN';

您可以内联执行此操作，而无需进行所有这些交换，如下所示:

SET DATEFORMAT DMY; -- the key you missed in your attempt I think
ALTER TABLE dbo.table ALTER COLUMN postdate DATE;

如果您无法修复列本身，那么至少考虑在该列周围添加检查约束和一些验证，以便明天坏数据不会返回那里。例如:

ALTER TABLE dbo.table
  ADD CONSTRAINT prevent_bad_dates
  CHECK (CONVERT(DATE, postdate, 103) >= '19000101');

在插入之前进行验证，假设您将 varchar 参数带入执行插入的存储过程:

ALTER PROCEDURE dbo.whatever
  @postdate NVARCHAR(10), -- assuming here
  @other_params...
AS
BEGIN
  SET NOCOUNT ON;
  SET DATEFORMAT DMY;

  IF ISDATE(@postdate) = 0
  BEGIN
    RAISERROR('Incorrect format for date: %s.', 11, 1, @postdate);
    RETURN;
  END

  ... perform insert...
END

此仍然无法帮助您理解输入06/09/2013 的用户是否意味着 6 月 9 日或 9 月 6 日。因此，我敦促您从用户手中夺走徒手的临时条目，并强制他们使用日历控件或日期选择器或其他东西，这样您就可以完全控制格式。如果您需要将字符串文字发送到 SQL Server，最安全的格式是 YYYYMMDD。此外，您还应该注意使用 BETWEEN 而不是开放式范围的潜在问题。请阅读以下帖子:

• What do BETWEEN and the devil have in common?

• Bad habits to kick : mis-handling date / range queries