Re: [sqlite] unable to use date fields in sqlite
Note https://www.sqlite.org/datatype3.html says: "Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions." This is partially true. The date and time functions will optionally parse 'a timezone indicator of the form "[+-]HH:MM" or just "Z"'. The same functions will not create this optional form. i.e. strftime is missing '%z' as a conversion specifier. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unable to use date fields in sqlite
On Tue Jul 23, 2019 at 04:41:59PM +0200, Andreas Kretzer wrote: > Am 23.07.2019 um 16:22 schrieb Steve Leonard: > > > > 4)I have tried several combinations of creating a new field > > of type numeric, blob, real, and integer and then tried to update If you want to work with date *strings* of the form '-mm-dd' you probably want to use TEXT. > > 3) I want to select all records where myfdt is between 11/05/2011 and > > 12/30/2011 have had no success > > Could someone please post the correct sql statement to do this? > > > > queries like this give no results: > > select * from mytable > > where myfdt > '11/06/2011' > > and myfdt < '04/13/2018' If you have modified your data to be -mm-dd then your queries should also be in that format: SELECT * FROM mytable WHERE myfdt > '2011-11-06' AND myfdt < '2018-04-13' -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unable to use date fields in sqlite
Hi Steve, beside what I said in my last statement, you should also have a look at https://www.sqlite.org/datatype3.html https://www.sqlite.org/lang_datefunc.html This may help you to understand, how SQLite handles specific data types, that might be understood (or at least be handled) different than in other DB engines. Andreas Am 23.07.2019 um 16:22 schrieb Steve Leonard: > > 1) I imported a new table using dbbrowser for sqlite from a csv file > one of the fields is named myfdat and the first record contains > '12/01/2011' > > 2) the import created a table with the field myfdt as type text and > the value matches the csv file > > 3) I want to select all records where myfdt is between 11/05/2011 and > 12/30/2011 have had no success > Could someone please post the correct sql statement to do this? > > queries like this give no results: > select * from mytable > where myfdt > '11/06/2011' > and myfdt < '04/13/2018' > > 4)I have tried several combinations of creating a new field > of type numeric, blob, real, and integer and then tried to update > this new field using several date functions, among them: > update mytable > set myfdt = > substr(myfdt, 7) || "-" || substr(myfdt,4,2) || "-" || substr(myfdt, > 1,2) > the sql ran ok, but the data in the new field is in the -dd-mm format > > depending on which combination I used, I get either no results > or I get wrong answers because a date like 12/04/2011 is > treat like it is April 12 instead of December 4th. > > Thank you. > Steve > > ___ > 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
Re: [sqlite] unable to use date fields in sqlite
Am 23.07.2019 um 16:22 schrieb Steve Leonard: > > 1) I imported a new table using dbbrowser for sqlite from a csv file > one of the fields is named myfdat and the first record contains > '12/01/2011' > > 2) the import created a table with the field myfdt as type text and > the value matches the csv file > > 3) I want to select all records where myfdt is between 11/05/2011 and > 12/30/2011 have had no success > Could someone please post the correct sql statement to do this? > > queries like this give no results: > select * from mytable > where myfdt > '11/06/2011' > and myfdt < '04/13/2018' > > 4)I have tried several combinations of creating a new field > of type numeric, blob, real, and integer and then tried to update > this new field using several date functions, among them: > update mytable > set myfdt = > substr(myfdt, 7) || "-" || substr(myfdt,4,2) || "-" || substr(myfdt, > 1,2) > the sql ran ok, but the data in the new field is in the -dd-mm format You are basically on a "good" way - just swap day and month field (-mm-dd) and you're done. This is still a string though ... update mytable set myfdt = substr(myfdt, 7) || "-" || substr(myfdt,1,2) || "-" || substr(myfdt, 4,2) > > depending on which combination I used, I get either no results > or I get wrong answers because a date like 12/04/2011 is > treat like it is April 12 instead of December 4th. If you want to deal with dates like this, you must "order" the parts of that string in descending order! Andreas > > Thank you. > Steve > > ___ > 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] unable to use date fields in sqlite
1) I imported a new table using dbbrowser for sqlite from a csv file one of the fields is named myfdat and the first record contains '12/01/2011' 2) the import created a table with the field myfdt as type text and the value matches the csv file 3) I want to select all records where myfdt is between 11/05/2011 and 12/30/2011 have had no success Could someone please post the correct sql statement to do this? queries like this give no results: select * from mytable where myfdt > '11/06/2011' and myfdt < '04/13/2018' 4)I have tried several combinations of creating a new field of type numeric, blob, real, and integer and then tried to update this new field using several date functions, among them: update mytable set myfdt = substr(myfdt, 7) || "-" || substr(myfdt,4,2) || "-" || substr(myfdt, 1,2) the sql ran ok, but the data in the new field is in the -dd-mm format depending on which combination I used, I get either no results or I get wrong answers because a date like 12/04/2011 is treat like it is April 12 instead of December 4th. Thank you. Steve ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
