Hi, Jose,
Re: Wishing out loud.
Maybe an implementation of sscanf() would be more useful generally.
As to your date parsing problem, if you really insist on doing it in sql,
you may already know how to accomplish it with something like the UPDATE
below:
.mode column
.headers on
create table t1 (dt, dtIso); -- I'm assuming you meant input (dt) to be in
m-d-yyyy format (and not d-m-yyyy)
INSERT INTO t1 VALUES
('2/1/2017', NULL),
('2/19/2019', NULL),
('12/5/1955', NULL),
('12/13/2018', NULL)
;
UPDATE t1 SET dtISO = printf('%04d-%02d-%02d',
substr(dt, -4, 4),
CAST(dt AS INTEGER),
CAST ( replace(substr(dt, 3), '/', ' ') AS INTEGER)
);
SELECT * FROM t1;
-- output, using sqlite3.exe version 3.29.0, was:
dt dtIso
---------- ----------
4/5/2019 2019-04-05
2/19/2019 2019-02-19
12/5/1955 1955-12-05
12/13/2018 2018-12-13
You can then check for a valid date with something like:
SELECT date('2019-12-32') isnull;
but if it's user input, you'd be sanitizing your inputs before they reach
sqlite, I should hope.
Donald
>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
