Thanks, I was able to patch the table by removing the single tick marks using the trim and the group by query now works as expected.
-----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Thursday, March 22, 2018 7:23 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] How to convert a datetime column to a date? 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
