On 2018/03/22 4:19 PM, David Raymond wrote:
Use datetime there instead of date or you'll lose the time part of it.
UPDATE foo SET dttm = datetime(trim(dttm,''''));
Absolutely, thanks for catching that - I kind of assumed he meant to
only have the date part, but that is of course in the final query and
not in the data.
Final query should then shorten to the actual date part to be:
SELECT date(dttm) AS dt, max(i) AS max_i FROM foo GROUP BY date(dttm) ORDER BY
1;
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
