1

I'm trying to convert/parse a string date of a format: MM/DD/YYYY or M/D/YYYY to a DATE("YYYY-MM-DD") in a column.

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;

I would love to use Regex, but SQLite doesn't support it. Any suggestion?

Nicolas
  • 79
  • 7
  • If you can convert the date column so that all dates have same length (ie. `05/09/1967` instead of `5/9/1967`) your conversion can be done [using substring](https://sqlite.org/forum/info/7c79d9057f35851c). I would imagine such conversion needs maximum 18 queries (1..9 of days, and 1..9 of months). – MyICQ May 25 '22 at 22:26
  • Does this answer your question? [SQLLite strftime not reading column value](https://stackoverflow.com/questions/60673844/sqllite-strftime-not-reading-column-value) – DinoCoderSaurus May 26 '22 at 10:22
  • Thanks, Dino, for the code example. No, it's not exactly what I'm looking for. But you are right MySQL or other databases have way more options to parse strings. In the meantime, I found a way that works for me. See my response below. – Nicolas May 26 '22 at 12:34

1 Answers1

1

I found a way, maybe a bit convoluted but it works on my dataset on SQLite

Create the table and insert some test data:

Create table date_test ( string_date TEXT );

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

The query that transform the string date do the date data type (assuming that the year is always 4 digits):

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
Nicolas
  • 79
  • 7