将STRING类型pickup_time转为DATETIME时转换失败求助

我是SQL新手，练习时建了runner_orders表：

CREATE TABLE runner_orders (
  "order_id" INTEGER,
  "runner_id" INTEGER,
  "pickup_time" VARCHAR(19),
  "distance" VARCHAR(7),
  "duration" VARCHAR(10),
  "cancellation" VARCHAR(23)
);

INSERT INTO runner_orders
  ("order_id", "runner_id", "pickup_time", "distance", "duration", "cancellation")
VALUES
  ('1', '1', '2020-01-01 18:15:34', '20km', '32 minutes', ''),
  ('2', '1', '2020-01-01 19:10:54', '20km', '27 minutes', ''),
  ('3', '1', '2020-01-03 00:12:37', '13.4km', '20 mins', NULL),
  ('4', '2', '2020-01-04 13:53:03', '23.4', '40', NULL),
  ('5', '3', '2020-01-08 21:10:57', '10', '15', NULL),
  ('6', '3', 'null', 'null', 'null', 'Restaurant Cancellation'),
  ('7', '2', '2020-01-08 21:30:45', '25km', '25mins', 'null'),
  ('8', '2', '2020-01-10 00:15:02', '23.4 km', '15 minute', 'null'),
  ('9', '2', 'null', 'null', 'null', 'Customer Cancellation'),
  ('10', '1', '2020-01-11 18:50:20', '10km', '10minutes', 'null');

想把pickup_time字段改成DATETIME类型，执行这句：

ALTER TABLE runner_orders
ALTER COLUMN pickup_time DATETIME

结果报错：Conversion failed when converting date and/or time from character string.

试过这些方法都没解决：

• 把日期格式改成不带短横线的'20200101 18:15:34'
• 把NULL改成0
• 日期时间中间加T，改成'2020-01-10T00:15:02'
• 用设计器手动改数据类型

问题核心是表里的pickup_time有字符串'null'（不是真正的SQL NULL值），SQL Server没法把这个字符串转成DATETIME。按以下步骤操作：

1. 先把所有字符串'null'替换成真正的NULL：

UPDATE runner_orders
SET pickup_time = NULL
WHERE pickup_time = 'null';

如果还有空字符串''也影响转换，一起处理：

UPDATE runner_orders
SET pickup_time = NULL
WHERE pickup_time = '' OR pickup_time = 'null';

2. 再执行修改字段类型的语句：

ALTER TABLE runner_orders
ALTER COLUMN pickup_time DATETIME;

这样就能成功完成类型转换了。

内容的提问来源于stack exchange，提问作者Ale