sqlite解析字符串日期至日期类型

Question

我正在尝试将格式的字符串日期转换/解析：mm/dd/yyyy或m/d/yyyy在列中的日期（“ yyyy-mm-dd”）。

Create table date_test ( string_date TEXT );

insert into date_test values ('5/17/1967'), ('3/7/1936'), ('10/20/1930');

select string_date, "function to parse the string" as date_as_date from date_test;

-- Expected Result:
--------------------------------------
--string_date   date_as_date
--------------------------------------
--5/17/1967     1967-05-17
--3/7/1936      1936-03-07
--10/20/1930    1930-10-20

-- I was trying to parse the date, but it gets a bit out of hand with the year:
SELECT substr('5/17/1967', 0, INSTR('5/17/1967', '/')) as Month;

SELECT substr(substr('5/17/1967', INSTR('5/17/1967', '/')+1), 0, INSTR(substr('5/17/1967', INSTR('5/17/1967', '/')+1), '/')) as Day;

我很想使用Regex，但是Sqlite不支持它。有建议吗？

Answer 1

我找到了一种方法，也许有点令人费解，但它在我的数据集上工作，在sqlite上

创建表并插入一些测试数据：

Create table date_test ( string_date TEXT );

insert into date_test values
('5/17/1967'),
('3/7/1936'),
('10/20/1930');

转换字符串日期执行日期数据类型的查询（假设该年度始终是4位数字）：

select string_date,
CASE
    WHEN length(Month) = 1 and length(Day) = 1 THEN DATE(Year || '-0' || Month || '-0' ||Day)
    WHEN length(Month) = 1 and length(Day) = 2 THEN DATE(Year || '-0' || Month || '-' ||Day)
    WHEN length(Month) = 2 and length(Day) = 1 THEN DATE(Year || '-' || Month || '-0' ||Day)
    WHEN length(Month) = 2 and length(Day) = 2 THEN DATE(Year || '-' || Month || '-' ||Day)
END AS date_as_date
from 
(select string_date
, substr(string_date, 0, INSTR(string_date, '/')) as Month
, substr(substr(string_date, INSTR(string_date, '/')+1), 0, INSTR(substr(string_date, INSTR(string_date, '/')+1), '/')) as Day
, substr(string_date, length(string_date)-3, length(string_date)) as Year
from date_test);


-- Result expected:
--------------------------------------
--string_date   date_as_date
--------------------------------------
--5/17/1967     1967-05-17
--3/7/1936      1936-03-07
--10/20/1930    1930-10-20