Use datetime there instead of date or you'll lose the time part of it. UPDATE foo SET dttm = datetime(trim(dttm,''''));
http://www.sqlite.org/datatype3.html -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Thursday, March 22, 2018 10:12 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] How to convert a datetime column to a date? Ok, that tells us exactly what we needed to know, and Peter was right, those quotes are in the actual data, and shouldn't be. Try this query please: UPDATE foo SET dttm = date(trim(dttm,'''')); -- That's 4 single quotes in a row, which is a single quote, escaped with another single quote, inside two single quotes. Followed by your original: SELECT dttm, max(i) AS max_i FROM foo GROUP BY dttm ORDER BY dttm; _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
