日期格式转换失败

Question

我有一个nvarchar(100)列，它的值为' 8/11/2022'。

我收到一个错误时，试图将其转换为日期…

select convert(date,[date],103)
from [Source].[TableName] s_p 
--Msg 241, Level 16, State 1, Line 96
--Conversion failed when converting date and/or time from character string.

我尝试了很多不同的方法来接近，但我找不到一个给我'08/11/2022'

select Date = REPLACE(LEFT([Date],10),' ','0')
from [Source].[TableName] s_p 
--Outcome  8/11/2022
select REPLACE([DATE],' 8/','08/')
from [Source].[TableName] s_p 
--Outcome  8/11/2022
select convert(nvarchar,[date],103) 
from [Source].[TableName] s_p
--Outcome  8/11/2022

奇怪的是，当我从结果网格复制和粘贴，然后做一个替换它工作得很好…

select REPLACE(' 8/11/2022',' 8/','08/')
--Outcome 08/11/2022

请帮助我得到'08/11/2022'或任何个位数，以有一个领先的0。

谢谢,将

Answer 1

不同的语言和文化对日期值有自己的格式偏好。有些地方，比如M/dd/yyyy。有些地方，比如dd/MM/yyyy。或者可能是d-M-YYYY(不同的分隔符和前导零的约定)。关键是，进入一个地方，把我们自己的偏好和规范强加给那个文化是不行的。

SQL语言也不例外。它真的是它自己的语言，因此它对日期处理有自己的期望。如果你违背了这些期望，那么当结果出现误解时，你不应该感到惊讶。

---

第一个期望是将日期和日期时间值存储在日期时间列中。很难低估这对性能和正确性产生的影响。

---

但是让我们假设这不是一个选项，并且您别无选择，只能使用像varchar或nvarchar这样的字符串列。在这种情况下，对于日期值应该如何格式化仍然存在期望。

如果您使用按长度降序存储日期部分的格式，任何数据库都会做得更好。例如，ISO-8601yyyy-MM-ddTHH:mm:sss[.fff]允许大于/小于比较工作是很重要的，它可以极大地帮助索引和性能，并且它使转换/转换操作到日期时间值更有可能成功和准确。

对于SQL Server，有三种可接受的格式:
yyyy-MM-ddTHH:mm:sss[.fff]，
yyyyMMdd HH:mm:ss[.fff]和
yyyyMMdd。

其他将具有不能按预期解析的日期值。任何调用CONVERT()方法的字符串操作都应该集中于达到这些格式之一。

考虑到这一点，并假设8/11/2022表示11月8日而不是8月11日(给定103转换格式)，您需要这样做:

convert(datetime, 
right([date], charindex('/', reverse([date]))-1) -- year
+ right('0' + replace(substring([date], charindex('/', [date])+1, 2), '/', ''), 2) -- month
+ right('0' + left([date], charindex('/',[date])-1),2)  -- day
)

你可以看到它在这里工作:

https://dbfiddle.uk/lM8sVySh

是的，代码太多了。而且速度也不只是一点点慢。再说一次，它之所以如此缓慢和复杂，是因为你跳进了自己的文化期望，没有尊重你正在使用的平台的语言。

---

最后，我需要质疑这个前提。正如上面的代码所示，SQL Server非常乐意转换这个特定的值而不会出现错误。这告诉我您可能有更多行，并且任何错误实际上都来自不同的行。

考虑到这一点，需要记住的一件事是WHERE子句条件不一定会在SELECT子句中的CONVERT()操作之前运行或过滤表。也就是说，如果这个表中有许多不同类型的值，那么无论使用哪种WHERE子句，都不能保证CONVERT()表达式只在日期值上运行。数据库不能以这种方式保证操作顺序。

问题也可能是一些不可见的unicode空白。

另一种可能是日期格式。大多数喜欢以日而不是月或年为开头的文化，也倾向于在第一个地方看到开头的0。这里缺少一个0，这让我怀疑列中是否有一些日期是按美国人的格式设置的。因此，然后您尝试解析具有02/13/2022和13/02/2022等值的列。显然，这两个日期不能使用相同的格式，因为没有第13个月。

在这种情况下，祝你好运，因为你不再有任何方法确定2/3/2022是指3月2日还是2月3日…尝试猜测(比如，假设你自己的通用格式)只是加剧了最初让你陷入混乱的同样的错误。

值得注意的是，如果从一开始就使用DateTime列，这三种可能性都可以避免。

Answer 2

如果您想更改为日期数据类型，则需要使用LPAD将0添加到string，然后将CAST() string作为日期