Is there an easy way to "fix" the data already in the table?
-----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Peter Da Silva Sent: Thursday, March 22, 2018 7:09 AM To: SQLite mailing list Subject: Re: [sqlite] How to convert a datetime column to a date? SQLITE3 datetime fields are actually text. Fix the file that you're importing so it doesn't have quotes around the date. It looks like you're using some kind of modified CSV exporter to create the file. On 3/22/18, 9:06 AM, "sqlite-users on behalf of Ron Watkins" <sqlite-users-boun...@mailinglists.sqlite.org on behalf of rwa...@gmail.com> wrote: The file that I use ".import" on contains records like this: '2018-03-22 07:01:01'|2533268 I had assumed the value was being treated as a datetime, but it looks like it may be treated as a string? Im a bit confused because the column definition sais "datetime", not "varchar", so if it's being treated as a string what can I do to fix it to be treated as a actual datetime datatype? -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Peter Da Silva Sent: Thursday, March 22, 2018 6:49 AM To: SQLite mailing list Subject: Re: [sqlite] How to convert a datetime column to a date? 1. It looks like you have superfluous quotes around the dates. That's probably your issue. 2. Try "select distinct dttm from foo LIMIT 10;" On 3/22/18, 8:47 AM, "sqlite-users on behalf of Ron Watkins" < <mailto:sqlite-users-boun...@mailinglists.sqlite.org%20on%20behalf%20of%20rwa...@gmail.com> sqlite-users-boun...@mailinglists.sqlite.org on behalf of rwa...@gmail.com> wrote: sqlite> select distinct dttm from foo; … (lots of records) '2018-03-22 06:25:01' '2018-03-22 06:26:01' '2018-03-22 06:27:01' '2018-03-22 06:28:01' '2018-03-22 06:29:01' '2018-03-22 06:30:01' '2018-03-22 06:31:01' '2018-03-22 06:32:02' '2018-03-22 06:33:01' '2018-03-22 06:34:01' '2018-03-22 06:35:01' '2018-03-22 06:36:01' '2018-03-22 06:37:01' '2018-03-22 06:38:01' '2018-03-22 06:39:01' '2018-03-22 06:40:01' '2018-03-22 06:41:01' '2018-03-22 06:42:01' '2018-03-22 06:43:01' '2018-03-22 06:44:01' sqlite> BTW, is there some equilivant to “select top 10 * from foo;” style to reduce the number of records? I tried this “top 10” but it’s apparently not supported. -----Original Message----- From: sqlite-users [ <mailto:sqlite-users-boun...@mailinglists.sqlite.org> mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Peter Da Silva Sent: Thursday, March 22, 2018 6:33 AM To: SQLite mailing list Subject: Re: [sqlite] How to convert a datetime column to a date? It might be helpful to provide some examples of what you have in those DATETIME columns. _______________________________________________ sqlite-users mailing list < <mailto:sqlite-users@mailinglists.sqlite.org> mailto:sqlite-users@mailinglists.sqlite.org> <mailto:sqlite-users@mailinglists.sqlite.org> sqlite-users@mailinglists.sqlite.org < <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list <mailto:sqlite-users@mailinglists.sqlite.org> sqlite-users@mailinglists.sqlite.org <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list <mailto:sqlite-users@mailinglists.sqlite.org> sqlite-users@mailinglists.sqlite.org <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> 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
