如何向DATE数据类型列插入DDMMYYYY格式的日期值

1. DATE_OF_BIRTH字段为DATE类型，数据库默认不识别DDMMYYYY格式的字符串输入
2. 插入值中存在非标准7位日期串6031987，缺失日的前导零，不符合8位DDMMYYYY规则，这是之前转换失败的主要原因

通用处理逻辑：

• 第一步：统一补全日期字符串长度，把7位的日期串补前导零转为标准8位DDMMYYYY格式
• 第二步：使用对应数据库的日期转换函数，指定输入格式为DDMMYYYY完成转换

不同数据库的具体实现 #

SQL Server #

使用RIGHT('0'+日期串,8)补前导零，搭配CONVERT函数指定格式码103（对应DD/MM/YYYY格式，无分隔符也可识别）：

INSERT INTO TEMP_CUSTOMERS (CUSTOMER_NAME, CUSTOMER_ID,
                            CUSTOMER_OPEN_DATE, LAST_CUNSULTED_DATE,
                            VACCINATION_TYPE, DOCTOR_CONSULTED,
                            STATE, COUNTRY, DATE_OF_BIRTH, ACTIVE_CUSTOMER) 
VALUES 
('ALEX', '123457', '20101012', '20121013', 'MVD', 'PAUL', 'SA', 'USA', CONVERT(DATE, RIGHT('0'+'06031987',8), 103), 'A'),
('JOHN', '123458', '20101012', '20121013', 'MVD', NULL, 'TN', 'IND', CONVERT(DATE, RIGHT('0'+'06031987',8), 103), 'A'),
('MATHEW', '123459', '20101012', '20121013', 'MVD', NULL, 'WAS', 'PHIL', CONVERT(DATE, RIGHT('0'+'06031987',8), 103), 'A'),
('MATT', '12345', '20101012', '20121013', 'MVD', NULL, 'BOS', 'NYC', CONVERT(DATE, RIGHT('0'+'06031987',8), 103), 'A'),
('JACOB', '1256', '20101012', '20121013', 'MVD', NULL, 'VIC', 'AU', CONVERT(DATE, RIGHT('0'+'6031987',8), 103), 'A')

MySQL #

使用LPAD(日期串,8,'0')补前导零，搭配STR_TO_DATE指定格式码%d%m%Y：

INSERT INTO TEMP_CUSTOMERS (CUSTOMER_NAME, CUSTOMER_ID,
                            CUSTOMER_OPEN_DATE, LAST_CUNSULTED_DATE,
                            VACCINATION_TYPE, DOCTOR_CONSULTED,
                            STATE, COUNTRY, DATE_OF_BIRTH, ACTIVE_CUSTOMER) 
VALUES 
('ALEX', '123457', '20101012', '20121013', 'MVD', 'PAUL', 'SA', 'USA', STR_TO_DATE(LPAD('06031987',8,'0'),'%d%m%Y'), 'A'),
('JOHN', '123458', '20101012', '20121013', 'MVD', NULL, 'TN', 'IND', STR_TO_DATE(LPAD('06031987',8,'0'),'%d%m%Y'), 'A'),
('MATHEW', '123459', '20101012', '20121013', 'MVD', NULL, 'WAS', 'PHIL', STR_TO_DATE(LPAD('06031987',8,'0'),'%d%m%Y'), 'A'),
('MATT', '12345', '20101012', '20121013', 'MVD', NULL, 'BOS', 'NYC', STR_TO_DATE(LPAD('06031987',8,'0'),'%d%m%Y'), 'A'),
('JACOB', '1256', '20101012', '20121013', 'MVD', NULL, 'VIC', 'AU', STR_TO_DATE(LPAD('6031987',8,'0'),'%d%m%Y'), 'A')

Oracle #

使用LPAD(日期串,8,'0')补前导零，搭配TO_DATE指定格式码DDMMYYYY：

INSERT INTO TEMP_CUSTOMERS (CUSTOMER_NAME, CUSTOMER_ID,
                            CUSTOMER_OPEN_DATE, LAST_CUNSULTED_DATE,
                            VACCINATION_TYPE, DOCTOR_CONSULTED,
                            STATE, COUNTRY, DATE_OF_BIRTH, ACTIVE_CUSTOMER) 
VALUES 
('ALEX', '123457', '20101012', '20121013', 'MVD', 'PAUL', 'SA', 'USA', TO_DATE(LPAD('06031987',8,'0'),'DDMMYYYY'), 'A'),
('JOHN', '123458', '20101012', '20121013', 'MVD', NULL, 'TN', 'IND', TO_DATE(LPAD('06031987',8,'0'),'DDMMYYYY'), 'A'),
('MATHEW', '123459', '20101012', '20121013', 'MVD', NULL, 'WAS', 'PHIL', TO_DATE(LPAD('06031987',8,'0'),'DDMMYYYY'), 'A'),
('MATT', '12345', '20101012', '20121013', 'MVD', NULL, 'BOS', 'NYC', TO_DATE(LPAD('06031987',8,'0'),'DDMMYYYY'), 'A'),
('JACOB', '1256', '20101012', '20121013', 'MVD', NULL, 'VIC', 'AU', TO_DATE(LPAD('6031987',8,'0'),'DDMMYYYY'), 'A')

全数据库兼容方案 #

如果需要兼容多种数据库，可以直接把补全后的8位DDMMYYYY字符串拆分为YYYY-MM-DD格式的ISO标准日期字符串，所有数据库都可以直接识别不需要转换函数，比如06031987处理后为1987-03-06。

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