提问人：bk171 提问时间：8/29/2023 最后编辑：marc_sbk171 更新时间：8/29/2023 访问量：139
在 SQL 中将简单文本列日期转换为日期
Convert a simple text column date to a date in SQL
问：
我需要将这种类型的列转换为日期。列中的数据如下所示：。nvarcharF209222018
我想转换为日期并使其看起来像 .09/22/2018
我试过这个，但语法不正确。
SELECT
f2,
CAST(f2, AS datetime)
FROM
Testbk
我也试过这个：
SELECT
f2,
CONVERT(nvarchar, f2, 101)
FROM
Testbk
再次 - 没有成功的转换。
答：
1赞
Sergey
8/29/2023
#1
不完全确定，您想要实现什么：将值转换为日期或以另一种格式将其置换。 请看下文
declare @InF2 varchar(8)='09222018';
select @InF2,LEFT(@iNf2,2)+'/'+SUBSTRING(@InF2,3,2)+'/'+SUBSTRING(@InF2,5,4)AS VARCHAR_USA_DATE,
CONVERT(DATE,LEFT(@iNf2,2)+'/'+SUBSTRING(@InF2,3,2)+'/'+SUBSTRING(@InF2,5,4),101)AS DATE_converted
评论
0赞
bk171
8/29/2023
那行得通。我有 70k 多条记录的列，其格式类似于 09222018，并希望将其转换为日期字段。
0赞
bk171
8/29/2023
它返回大约 15k 行，但我收到此错误。消息 241，级别 16，状态 1，第 7 行 从字符串转换日期和/或时间时转换失败。完成时间：2023-08-28T13：20：58.3685497-05：00 那是因为该字段某处的数据不好吗？
0赞
Sergey
8/29/2023
最有可能。您可以查看TRY_CONVERT功能来查找此可疑数据
0赞
bk171
8/29/2023
我将如何为这个领域做到这一点？对不起，我以前从未使用过 try-convert 函数。
0赞
Sergey
8/29/2023
从your_table中选择 try_convert（date，LEFT（f2,2）+'/'+SUBSTRING（F2,3,2）+'/'+SUBSTRING（F2,5,4），101） 其中 try_convert（date，LEFT（f2,2）+'/'+SUBSTRING（F2,3,2）+'/'+SUBSTRING（F2,5,4），101） 为 null
0赞
Jiji
8/29/2023
#2
你可以试试这个：
Select convert(varchar(10),cast(STUFF(STUFF(f2, 3,0,'/'),6,0,'/')as
datetime),101)as f2 from Testbk
请看这里： https://dbfiddle.uk/VUDoJYl8
评论
0赞
Stu
8/29/2023
为什么已知输出为 10 个字符？varchar(50)
0赞
Jiji
8/29/2023
@Stu 是的，我明白你的意思。在这里发布答案之前，我在 db 小提琴上尝试了一些东西。但是忘了纠正长度。我正在根据您的通知编辑答案。谢谢。
0赞
Mark Schultheiss
8/29/2023
#3
要做到这一点，请尝试将转换与输出格式分开 - 即使它们位于一个查询中。我使用了一些与旧SQL兼容的语法，可能是因为没有指定版本。
一个示例：
DECLARE @D NVARCHAR(8) = '09222018';
DECLARE @DT DATETIME;
SELECT SUBSTRING(@D, 5, 4), SUBSTRING(@D, 3, 2),SUBSTRING(@D,1, 2);
SELECT @DT = CAST(CONCAT(SUBSTRING(@D, 5, 4),'-', SUBSTRING(@D, 1, 2) ,'-', SUBSTRING(@D,3, 2)) AS DATETIME);
SELECT FORMAT(@DT,'MM-dd-yyyy');
0赞
Thom A
8/29/2023
#4
正如我在评论中提到的，你真正需要做的是修复你的设计。此处显示您的列以 格式表示日期，因此我们需要将您的列更改为数据类型。MMddyyyydate
为了使您的列成为正确的数据类型，我们需要将其转换为明确的格式，理想情况下。为此，我们需要以明确的格式将其转换为基于字符串的值，然后返回到基于字符串的值。然后我们可以将列转换为正确的数据类型。yyyyMMddCONVERTdateALTER
不幸的是，没有样式代码，但有 ： .将您的值更改为此值并不太困难，只需几秒钟即可。然后我们可以尝试将值转换为 a（如果它不是有效日期，则该值将丢失），然后我们可以将其转换回 .MMddyyyyMM/dd/yyyy101STUFFTRY_CONVERTdateCONVERTvarchar
不过，我首先添加一个新列，因为这将存储原始值，您可以检查这些值以尝试恢复任何错误数据。
ALTER TABLE dbo.Testbk ADD StringDate varchar(8); --length assumes all MMddyyyy
UPDATE dbo.Testbk
SET StringDate = f2;
GO
UPDATE dbo.Testbk
SET f2 = CONVERT(varchar(8),CONVERT(date,STUFF(STUFF(f2,5,0,'/'),3,0,'/'),101),112);
ALTER TABLE dbo.Testbk ALTER COLUMN f2 date /*NOT NULL*/;
现在你的设计是固定的，你可以担心值在表示层中的呈现方式，因为你的应用程序将接收日期值的 * binary* 值，并能够按照你喜欢的方式正确设置它的格式。
评论
cast(f2 as datetime)CONVERT(DATE,CONCAT(RIGHT(f2,4),LEFT(f2,4)),112)