34,623
社区成员
![](https://csdnimg.cn/release/cmsfe/public/img/topic.427195d5.png)
![](https://csdnimg.cn/release/cmsfe/public/img/me.40a70ab0.png)
![](https://csdnimg.cn/release/cmsfe/public/img/task.87b52881.png)
![](https://csdnimg.cn/release/cmsfe/public/img/share-circle.3e0b7822.png)
这个数据也挺奇怪,咋存进来的呢?
USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[出库时间] VARCHAR(50)
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'20220303 101656')
INSERT INTO dbo.[t] VALUES(N'20220303 101704')
INSERT INTO dbo.[t] VALUES(N'20220307 084431')
INSERT INTO dbo.[t] VALUES(N'20220307 084448')
INSERT INTO dbo.[t] VALUES(N'20220307 084406')
INSERT INTO dbo.[t] VALUES(N'20220307 084419')
INSERT INTO dbo.[t] VALUES(N'2022/3/3')
INSERT INTO dbo.[t] VALUES(N'2022/3/3')
INSERT INTO dbo.[t] VALUES(N'2022/3/3')
INSERT INTO dbo.[t] VALUES(N'2022/3/3')
INSERT INTO dbo.[t] VALUES(N'2022/3/3')
INSERT INTO dbo.[t] VALUES(N'2022/3/3')
INSERT INTO dbo.[t] VALUES(N'2022/3/4')
INSERT INTO dbo.[t] VALUES(N'2022/3/4')
INSERT INTO dbo.[t] VALUES(N'2022/3/4')
INSERT INTO dbo.[t] VALUES(N'2022/3/4')
INSERT INTO dbo.[t] VALUES(N'2022/3/4')
INSERT INTO dbo.[t] VALUES(N'2022/3/4')
INSERT INTO dbo.[t] VALUES(N'2022/3/4')
INSERT INTO dbo.[t] VALUES(N'2022/3/4')
INSERT INTO dbo.[t] VALUES(N'2022/3/4')
INSERT INTO dbo.[t] VALUES(N'2022/3/4')
INSERT INTO dbo.[t] VALUES(N'2022/3/4')
INSERT INTO dbo.[t] VALUES(N'20220311 094024')
INSERT INTO dbo.[t] VALUES(N'20220309 093017')
INSERT INTO dbo.[t] VALUES(N'20220309 092954')
----------- 以上为测试表及测试数据 -----------
SELECT *,
CASE WHEN [出库时间] LIKE '% %' THEN
CAST(STUFF(STUFF(STUFF(STUFF([出库时间],5,0,'-'),8,0,'-'),14,0,':'),17,0,':') AS DATETIME)
ELSE CAST([出库时间] AS DATETIME) END AS [出库时间2]
FROM t
先截取字符串,把空格之前的字符截出来,然后转换字段类型为 datetime 即可
你现在的类型是 varchar 这种字符串类型吧。
转换不难,关键还是要定义成 datetime 类型,一劳永逸。
下面的代码,你自己替换一下 tableName 为实际的表名吧。
--1、新增一列:
alter table tableName add 出库时间2 datetime
--2、更新新列为原来的值
update tableName
set 出库时间2=cast(出库时间 as datetime)
--3、删除旧列
alter table tableName drop column 出库时间
--4、新列改回为旧列名
exec sp_rename 'tableName.出库时间2','出库时间'