将varchar转换为缺少时间的datetime时，按结果排序超出范围

Question

我在日志数据库中有一个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 |
|---------------------|

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

这些数据记录缺少月份的前0，并且一点在年份之后太多例如16.9.2020. 14:22:23

我快要把编程的人的脑袋扯下来了

有什么办法可以得到正确的数值吗​​还是通过查询返回？

很抱歉存在误解，并感谢迄今为止为解决问题做出贡献的每一个人

Answer 1

正如我在评论中提到的，实际上您应该修复您的数据类型。

如果您的所有日期都是格式的dd.MM.yyyy hh:mm:ss，则无论设置如何，都可以使用CONVERT和样式代码进行转换：

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';

Answer 2

让我们试试convert函数(使用SQL Server进行日期和时间转换(：

declare @my_date_and_time  varchar(19) = '17.09.2020 00:00:01';
select convert(date, @my_date_and_time, 104)

或者尝试将dateformat设置为dmy(天/月/年(:

set dateformat dmy
declare @my_date_and_time  varchar(19) = '17.09.2020 00:00:01';
select cast(@my_date_and_time as date)