时间戳

首页 > 解决方案 > 将 varchar 转换为缺少时间的日期时间时，按结果排序超出范围

sql-server - 将 varchar 转换为缺少时间的日期时间时，按结果排序超出范围

问题描述

我varchar在日志数据库中有一行通常看起来像17.09.2020 00:00:01，我可以毫无问题CAST()地对其进行排序，例如这个简化的查询：datetime

SELECT CAST(my_date_and_time AS datetime)
FROM my_table
ORDER BY CAST(my_date_and_time AS datetime) DESC

现在的问题是，今晚我在 00:00:00 有一个日志条目，并且日志记录脚本或数据库切断了该varchar字段的时间，所以现在它只包含17.09.2020而不是17.09.2020 00:00:00. 当我尝试将其强制转换datetime时，会导致超出范围的错误，可能是因为缺少时间部分？

不幸的是，我无权访问日志脚本来更改它。那么有什么方法可以将其转换为查询中的可用格式？

编辑：这是 my_date_and_time 行的示例：

|---------------------|
|   my_date_and_time  |
|---------------------|
| 17.09.2020 05:29:53 |
|---------------------|
| 17.09.2020 00:02:11 |
|---------------------|
|      17.09.2020     |  
|---------------------|
| 16.09.2020 23:59:38 |
|---------------------|
| 16.09.2020 23:59:18 |
|---------------------|

EDIT2：通过进一步测试，我能够进一步缩小错误范围。这不是因为最初假设的时间戳，它没有时间信息。该问题实际上会影响不合规的记录。

这些数据记录缺少前一个月的 0 和一年之后的一个点太多。 例如16.9.2020. 14:22:23

我快要扯掉编写这个程序的人的头了..

无论如何，有什么可以想象的方法可以通过查询取回正确的值吗？

很抱歉造成误解，并感谢迄今为止为解决问题做出贡献的所有人

标签： sql-serverdatetimevarcharoutofrangeexception

解决方案

正如我在评论中提到的，你真的应该修复你的数据类型。

如果您所有的日期都采用这种格式，dd.MM.yyyy hh:mm:ss那么无论设置如何，您都可以使用 useCONVERT和样式代码来转换它们：

SELECT TRY_CONVERT(datetime,Your_varchar,103)
FROM dbo.YourTable;

如果有任何返回NULL，它们要么有值NULL，要么转换失败。

但回到修复你的数据。

首先，让我们在新列中执行此操作：

ALTER TABLE dbo.YourTable ADD Actual_date_and_Time datetime NULL

现在我们可以UPDATE通过覆盖该值来获得该值：

UPDATE dbo.YourTable
SET Actual_date_and_Time = TRY_CONVERT(datetime,Varchar_Date_and_time,103);

然后你可以查询一个实际的datetime列。

然后，您可以查看您的新数据，看看是否有问题，然后修改：

SELECT Varchar_Date_and_time
FROM dbo.YourTable
WHERE Varchar_Date_and_time IS NOT NULL
  AND Actual_date_and_Time;

然后，最后，您可以DROP将旧列重命名为新列（如果您愿意）：

ALTER TABLE dbo.YourTable DROP COLUMN Varchar_Date_and_time;
EXEC sys.sp_rename N'dbo.YourTable.Actual_date_and_Time',N'Varchar_Date_and_time','COLUMN';

推荐阅读